Okay, so you are fairly new to Power BI and you want to consume data from Dynamics 365 using the OData API. Here’s how!
First you select Get Data, then select OData. You enter your credentials, select your entities and vóila! You have data tables in your model.
But now you are trying to calculate sales by month and year. How can we tie our tables to a specific date? Enter the Dynamics 365 date tables.
There are DAX scripts available (just Google them) to generate a date table programmatically. This comes in very handy. You must remember to select “Mark as Date Table” on the Modelling tab to make time-based calculations work.
Once “Mark as Date Table” is selected, a new prompt will appear. Select Date from the drop-down list.
Note: the column must be data type “date” and has contain only unique values.
Go ahead and create your relationship between the date table and the other tables in your model, matching on the fields of type “date.”
You continue starting to build out your first visual, adding calendar month to the axis and line amount to the value field.
Axis is showing as blank!
There must be an issue with the date table.
Navigate to the relationship of your date table.
We can see that there is a slight variance in the Date and InvoiceDate causing our visual to be off 12 AM vs 12 PM. This is due to the Invoice Date coming through as a DateTimeOffset Data type. This is a special datatime format used by OData. Since you want the OData sources to refresh every once in awhile, we can’t use PowerQuery to edit this field. Instead we will create a new calculated field to represent the Invoice Date Key.
On the SalesInvoiceHeaders table, create a new column.
InvoiceDateKey = IF(ISBLANK(SalesInvoiceHeaders[InvoiceDate]),0,INT(FORMAT(SalesInvoiceHeaders[InvoiceDate], “YYYYMMDD”)))
Navigate back to the model and update the relationship to match Date.DateKey and SalesInvoiceHeaders.InvoiceDateKey.
Return to the visual we created and notice the change. It now works as expected.
Have any questions about using date tables in Dynamics 365? Please contact us at any time!
This publication contains general information only and Sikich is not, by means of this publication, rendering accounting, business, financial, investment, legal, tax, or any other professional advice or services. This publication is not a substitute for such professional advice or services, nor should you use it as a basis for any decision, action or omission that may affect you or your business. Before making any decision, taking any action or omitting an action that may affect you or your business, you should consult a qualified professional advisor. In addition, this publication may contain certain content generated by an artificial intelligence (AI) language model. You acknowledge that Sikich shall not be responsible for any loss sustained by you or any person who relies on this publication.
About the Author
Justin Languirand
Solution Architect with a focus on Microsoft Dynamics CRM, Data Integration (Scribe), Microsoft Dynamics GP, SQL Server, SSRS, SSIS, Jitterbit. Justin works with clients to transform data into information they use to run their businesses.
Sign up for Insights
Join 14,000+ Business executives and decision makers.
Latest Insights
Dynamics 365
Choosing the Right Dynamics 365 CE Module: Sales, Customer S...
May 19, 2025
Technology
Using the Fabric API for GraphQL
April 30, 2025
Technology
Turning Shipping Into a Model of Efficiency and Simplicity
March 19, 2025
Dynamics 365
Managing Dynamics 365 Dataverse Storage
March 10, 2025
Dynamics 365
New Security Governance Features in Microsoft Dynamics 365
February 28, 2025
Dynamics 365
Infographic: Hagler Systems Drives Project Visibility and Ac...
February 10, 2025
Technology
From Paper to Automation: Rethinking Shop Floor Reporting
December 18, 2024
Dynamics 365
The Cost of Free Custom Code in Multi-Company Organizations
December 16, 2024
Technology
Only One Microsoft Dynamics 365 ERP System Is the Right Solu...
December 12, 2024
Dynamics 365
Improving Field Service Management through Connected Service...
November 21, 2024