How to easily add a date dimension table in Power BI in 1 minute
A date dimension table allows you to work with dates more easily and can eliminate the need for complex DAX expressions.
Creating a date dimension table in Power BI is straightforward: copy this DAX code into a new table, then customize as needed.
Calendar table =
CALENDAR(DATE( 2020, 1, 1 ), DATE( 2021, 12, 31 ) ), “DateAsInteger”, FORMAT ( [Date], “YYYYMMDD” ),
“Year”, YEAR( [Date] ),
“Monthnumber”, FORMAT ( [Date], “MM” ),
“YearMonthnumber”, FORMAT ( [Date], “YYYY/MM” ),
“YearMonthShort”, FORMAT ( [Date], “YYYY/mmm” ),
“MonthNameShort”, FORMAT ( [Date], “mmm” ),
“MonthNameLong”, FORMAT ( [Date], “mmmm” ),
“DayOfWeekNumber”, WEEKDAY ( [Date] ),
“DayOfWeek”, FORMAT ( [Date], “dddd” ),
“DayOfWeekShort”, FORMAT ( [Date], “ddd” ),
“Quarter”, “Q” & FORMAT ( [Date], “Q” ),
“YearQuarter”, FORMAT ( [Date], “YYYY” ) & “/Q” & FORMAT ( [Date], “Q” ) )
)
The CALENDAR function above generates a list of all of the dates between the two date values entered, which you easily customise or substitute for a dynamic date range the references another dataset, we often use this with a maximum and minimum date from a fact table to make the date dimension fully dynamic
Once you have added the date dimension table you can set the relationships to it and use any of the extra date fields in your dashboards
Try out different options and see what works best for you.
Watch the video to see it in action, its only just over 1 minute long
Subscribe to our channel to see more Power BI Timesavers