Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Working with DAX Time Intelligence functions, documentation always mention having "consecutive dates". I am trying to determine if the ENTIRE dimDate table must have consecutive dates, OR the dates "in question" must be consecutive.
For instance when you have a dimDate table, Kimball method requires you to have an "Unknown" row in the relative Dimension table. In this instance, that "Unknown" date is 1/1/1900. So if you query the dimDate table, the date column begins at 1/1/1900 and then can jump to 1/1/2018. From 1/1/2018 onward the dates are consecutive until the specified end date (Example 12/31/2025).
Will DAX time intelligence functions work correctly with this type of table, or must ALL of the dates in the dimDate table be consecutive?
Solved! Go to Solution.
Thanks @mwegener for the article.
I reached out to Marco about the issue and he responded by telling me when using the tabular model it is best to avoid the default "Unknown" row. However, I can remove the "Unknown" row from the Dim Date table with Power Query. Technically this leaves a referential integrity issue in the model, but Power BI puts a "Blank" row in the Dim Table for any keys in the fact table that dont match in the Dim table.
This works for me as this keeps the Data Warehouse tool agnostic, maintains referential itegrity w/i the Data Warehouse and ensures the DAX time intelligence functions work correctly.
Hi,
Suppose you have a data field named OrderDate that contains the order data (not every day an order is placed). But the calendar should include consecutive all dates from the oldest to the most recent date of the data field OrderDate, no more, but no less.
Regards FrankAT
I would agree, however we are using the Kimball method for the data warehouse. So, typically what I see is a range that is enough to allow for growth, and historicals IE, 1/1/2017 - 12/31/2025.
There are other reports utilizing time intelligence features, though my confidence in them is lacking at this point, so I am looking for some clarification.
Hi @dscott73 ,
look at this.
https://www.sqlbi.com/articles/handling-wrong-or-missing-dates-in-tabular/
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Thanks @mwegener for the article.
I reached out to Marco about the issue and he responded by telling me when using the tabular model it is best to avoid the default "Unknown" row. However, I can remove the "Unknown" row from the Dim Date table with Power Query. Technically this leaves a referential integrity issue in the model, but Power BI puts a "Blank" row in the Dim Table for any keys in the fact table that dont match in the Dim table.
This works for me as this keeps the Data Warehouse tool agnostic, maintains referential itegrity w/i the Data Warehouse and ensures the DAX time intelligence functions work correctly.
Hi @dscott73 ,
If your question has been answered, please mark a post as solution, this will also help others.
Please give Kudos for support.
Hi @dscott73
I think that should answer your question.
https://docs.microsoft.com/en-us/power-bi/desktop-date-tables
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
That's true, but it is not required to mark a table as a Date table. My understanding is, marking a table as a Date table allows us to not have to wrap dimDate[Date] in ALL() in some scenerios.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |