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
ALeef
Advocate III
Advocate III

Possible Tip/Trick: Dynamic Date Dimension Table

I'm going to share the dynamic date dimension table I built, hopefully it is useful to someone!  I named it DateTable for this tutorial, and my fact table FactTable.

 

The table is built using the following formula:

DateTable = CALENDAR (MINX('FactTable', [Created Date]), NOW())

Basically, it looks for the oldest date you have for the column [created date], and builds a sequential date range until the time of refresh - so it always has calendar information for all data points in the set.

 

I added the following columns:

Date = This is built when you create the table

Year = YEAR([Date])

MonthofYear = MONTH([Date])

QuarterofYear = ROUNDUP (MONTH([Date])/3,0)

OrdinalDate = DATEDIFF([Year]&",1,1",[Date],DAY)+1

DayofWeek = WEEKDAY([Date],2)

ISOWeekofYear = IF ((([OrdinalDate] - [DayofWeek] + 10)/7) >= 53, 1, TRUNC(([OrdinalDate] - [DayofWeek] + 10)/7))

DayofMonth = DAY([Date])

 

Month = FORMAT([Date], "MMM") & " " & [Year])

Quarter = "Q" & [Quarter] & " " & [Year]

Week Ending = [Date] + (7- [DayofWeek])

Day = Format([Date],"DDDD")

 

 

With both the numeric and text values, you can use "Sort by" to properly order them in visuals.  If you want your week numbers to start on a Sunday instead of Monday, change the "1" to a "2" in ISOWeekofYear column - however ISO Standard is a Monday Start.

 

I'm still pretty new to PowerBI, but I'll help if I can!  If you have suggestions or modifications, I'd love to hear them.

 

EDIT:  I changed how the week number is calculated, so I can use ISO Week Numbers.  This also helped fix the "week Ending column, which broke when spanning multiple years that included a leap year.  Let me know if you find any problems!

 

EDIT2:  MS just released the timeline slicer for PowerBI.  That probably, mostly makes this table useless.  Read more here: http://blogs.msdn.com/b/powerbi/archive/2015/11/03/visual-awesomeness-unlocked-the-timeline-slicer.a...

 

39 REPLIES 39
ImkeF
Super User
Super User

Nice trick!

Just came across this blogpost using ADDCOLUMN allowing you to "script" the table-creation, so no need to create every single column manually: http://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF thanks so much! awesome 😄

faacq
Regular Visitor

I have generate a dynamic date table but I'm not able to create a relationship with the main table where my data resides. In both tables I have the NameofMonth and the Year.

When I try to manually create the relationship I get this error.  What am I doing wrong?

1.PNG

 

KHorseman
Community Champion
Community Champion

@faacq one of the columns in a relationship must have unique values. A month name column will have the same month name appear at least 28 times. Date tables are normally linked to fact tables via the unique date column. All other columns are supplemental info about the date in question, not meant for creating relationships.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you for the reply. I probably do not undestand the purpose of having a date table. I was expecting after establishing the link between my fact table and the date table, to use the slicers to filter my data in many different ways.

If establishing the relation is not needed, how can I use the date table to slice my data?

 

KHorseman
Community Champion
Community Champion

@faacq you understand the purpose correctly, but not the method. You're trying to create a relationship with a month column, which is incorrect. You're supposed to create a relationship between a date column in your fact table and the main date column in the date table. Then you could use the month column as a slicer.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




You are right, using the date column I managed to establish the relation. When I try to slice my data It seems that the date table does not have any effect. My fact table has a column with "Month Year" info, but still I'm not able to slice my data with the columns available in the date table.

 

1.PNG

@faacq I think your problem will be solved if you just turn the date column in your data to a date, eg. if it is 201601 just change it to 1/1/2016

 

In terms of using it in the slicer, make sure you pull the date column from the date table into the slicer, not the date column from the data

 

let me know if that helps 🙂

faacq
Regular Visitor

It worked, I established the relation between the date column in date.dim table and the date column in my fact table. I modified the content on the date column in my fact table from "Month" "year" to 01.01.2016.  I update the data type in PowerBI to (MMM yyyy)

Thank you for your help

Hi,

 

Now that I have this date table, I am having trouble actually using it.  How can I calculate a rolling calculation, so that it will count the number of an event 60 days from today's date?  Also, how to count the number of events in the future?

 

 

robcrockford
Frequent Visitor

Hi, new to PBI, when you say:

 

"The table is built using the following formula:

DateTable = CALENDAR (MINX('FactTable', [Created Date]), NOW())"

 

Where should this formula be added? Assume you create the column headers for the rest and add formulas into row 1?

 

Thanks in advance

 

@robcrockford please see picture below 🙂 

 

Capture.PNG

 

 

weilip1803
Frequent Visitor

I am having a problem, The formula above that I had used is sumerizing the data. How can I avoid the summary. The ordering of the column in charts are sorting the month names by alphabetical order how can i make it go by chronological order?

@weilip1803

 

It may not be elegant but I have added a 'MonthSort' column to my table

 

MonthSort = DateTable[Year]&IF(DateTable[MonthofYear]<10,0,"")&DateTable[MonthofYear]

 

Then simply select the column you're using on the chart in the data tab, click 'modeling' and select the 'Sort by Column' button

 

Let me know how that goes 🙂

 

 

Yea got it.Thanks ^^

tvermeer
Regular Visitor

I know this post is abit old but thanks so much!  Creating this date table has completely changed how I can link some of my sales and budgeting data based on dates.  Cheers!

mbdtz
Advocate III
Advocate III

This is still relevant and so awesome

 

I'm using this to join a set of data that only has discreet dates which don't allow use of the previousyear() functions etc

 

thanks for sharing 😄

 

ps. I also added an extra column:
FiscalYear = IF(MONTH(DateTable[Date]) > 6, DateTable[Year] &"/"&right(DateTable[Year],2)+1, DateTable[Year]-1 &"/"&right(DateTable[Year],2)) 

lanceengland
Regular Visitor

Nice tip. As usual, there are several ways to do the same thing with DAX. A function similar to CALENDAR is CALENDARAUTO which scans all the date columns and returns a range of dates from the earliest date to the latest date. It is very handy.

Anonymous
Not applicable

@lanceengland However, the CALENDARAUTO isn't dynamic right? I mean it scans for all the dates from earliest to the latest. But if the data source is updated, the CALENDARAUTO doesn't update automatically. Someone can correct me if i'm wrong. 

 

Edit: I just realized that CALENDARAUTO is in fact dynamic. Tried it with an Excel data source. Removed some dates and the table named DateTable (created with calendarauto) updated its date range after a refresh.

Yes. Dynamic is this sense is at process time i.e. refreshing the data model from the data source. On that note, while I haven't verified it, I'm guessing CALENDARAUTO would not work in Direct Query mode. That said, if you were in Direct Query mode, you would handle the dynamic date range at the data source.

 

 

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.