Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dscott73
Resolver I
Resolver I

Date Dimension Table with Default 1/1/1900 date. Must entire date column be consecutive

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?

1 ACCEPTED 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.

View solution in original post

7 REPLIES 7
FrankAT
Community Champion
Community Champion

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Hi @dscott73 

 

I think that should answer your question.

https://docs.microsoft.com/en-us/power-bi/desktop-date-tables

DateGaps.png

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.