DAX dDates table 2.0 | TMDL
Many years ago I made a DAX calendar blogpost. I’ve been using that calendar table for many reports since then. Now it’s on like page 4 of the blog or something and it’s annoying to make all the clicks needed to get to it. Clearly the best solution is to make a new blogpost and a Dates table version 2!
As with most version upgrades, this too comes with a set of improvements, but let’s review them as we go along. If you prefer to simply copy the TMDL script and get going, feel free to do so here! If you want to learn more about it, continue reading underneath the code!
createOrReplace
table dDates
column Date
formatString: yyyy-mm-dd
summarizeBy: none
isNameInferred
sourceColumn: [Date]
annotation SummarizationSetBy = Automatic
annotation UnderlyingDateTimeDataType = Date
annotation PBI_FormatHint = {"isDateTimeCustom":true}
column Year
formatString: 0
summarizeBy: none
isNameInferred
sourceColumn: [Year]
annotation SummarizationSetBy = Automatic
column Quarter
formatString: 0
summarizeBy: none
isNameInferred
sourceColumn: [Quarter]
annotation SummarizationSetBy = Automatic
column 'Month No'
formatString: 0
summarizeBy: none
isNameInferred
sourceColumn: [Month No]
annotation SummarizationSetBy = Automatic
column 'Month Name'
summarizeBy: none
isNameInferred
sourceColumn: [Month Name]
sortByColumn: 'Month No'
annotation SummarizationSetBy = Automatic
column Day
formatString: 0
summarizeBy: none
isNameInferred
sourceColumn: [Day]
annotation SummarizationSetBy = Automatic
column 'Week No'
formatString: 0
summarizeBy: none
isNameInferred
sourceColumn: [Week No]
annotation SummarizationSetBy = Automatic
column 'Week Day No'
formatString: 0
summarizeBy: none
isNameInferred
sourceColumn: [Week Day No]
annotation SummarizationSetBy = Automatic
column 'Week Day Name'
summarizeBy: none
isNameInferred
sourceColumn: [Week Day Name]
sortByColumn: 'Week Day No'
annotation SummarizationSetBy = Automatic
column 'Day Type'
summarizeBy: none
isNameInferred
sourceColumn: [Day Type]
annotation SummarizationSetBy = Automatic
column 'Offset Year'
formatString: 0
summarizeBy: none
isNameInferred
sourceColumn: [Offset Year]
annotation SummarizationSetBy = Automatic
column 'Offset Quarter'
formatString: 0
summarizeBy: none
isNameInferred
sourceColumn: [Offset Quarter]
annotation SummarizationSetBy = Automatic
column 'Offset Month'
formatString: 0
summarizeBy: none
isNameInferred
sourceColumn: [Offset Month]
annotation SummarizationSetBy = Automatic
column 'Offset Week'
formatString: 0
summarizeBy: none
isNameInferred
sourceColumn: [Offset Week]
annotation SummarizationSetBy = Automatic
column 'Offset Day'
summarizeBy: none
isNameInferred
sourceColumn: [Offset Day]
annotation SummarizationSetBy = Automatic
annotation PBI_FormatHint = {"isGeneralNumber":true}
column 'Date value'
summarizeBy: none
isNameInferred
sourceColumn: [Date value]
annotation SummarizationSetBy = Automatic
annotation PBI_FormatHint = {"isGeneralNumber":true}
column YearMonth
summarizeBy: none
isNameInferred
sourceColumn: [YearMonth]
annotation SummarizationSetBy = Automatic
annotation PBI_FormatHint = {"isGeneralNumber":true}
column 'Month Start'
formatString: yyyy-mm-dd
summarizeBy: none
isNameInferred
sourceColumn: [Month Start]
annotation UnderlyingDateTimeDataType = Date
annotation SummarizationSetBy = Automatic
annotation PBI_FormatHint = {"isDateTimeCustom":true}
column 'Month End'
formatString: yyyy-mm-dd
summarizeBy: none
isNameInferred
sourceColumn: [Month End]
annotation SummarizationSetBy = Automatic
annotation UnderlyingDateTimeDataType = Date
annotation PBI_FormatHint = {"isDateTimeCustom":true}
column 'Quarter Start'
formatString: yyyy-mm-dd
summarizeBy: none
isNameInferred
sourceColumn: [Quarter Start]
annotation SummarizationSetBy = Automatic
annotation UnderlyingDateTimeDataType = Date
annotation PBI_FormatHint = {"isDateTimeCustom":true}
column 'Quarter End'
formatString: yyyy-mm-dd
summarizeBy: none
isNameInferred
sourceColumn: [Quarter End]
annotation SummarizationSetBy = Automatic
annotation UnderlyingDateTimeDataType = Date
annotation PBI_FormatHint = {"isDateTimeCustom":true}
hierarchy '_Year Quarter Month No Day'
level Year
column: Year
level Quarter
column: Quarter
level 'Month No'
column: 'Month No'
level Day
column: Day
hierarchy '_Year Week Day'
level Year
column: Year
level 'Week No'
column: 'Week No'
level Day
column: Day
hierarchy '_Year Month No Day'
level Year
column: Year
level 'Month No'
column: 'Month No'
level Day
column: Day
hierarchy '_Year Month Name Day'
level Year
column: Year
level 'Month Name'
column: 'Month Name'
level Day
column: Day
hierarchy '_Month Name Year'
level 'Month Name'
column: 'Month Name'
level Year
column: Year
hierarchy '_Year Month Name'
level Year
column: Year
level 'Month Name'
column: 'Month Name'
partition dDates = calculated
mode: import
source = ```
// Set Number of years you want to start and end this table.
VAR YearsPrior = 5
VAR YearsFuture = 1
// Set the language you wish the DAX code to use when creating names on weeks, months etc.
VAR Locale = "sv-SE"
// Execute table calculations
VAR StartDate = DATE(YEAR(TODAY())-YearsPrior,1,1)
VAR EndDate = DATE(YEAR(TODAY())+YearsFuture,12,31)
VAR CalendarTable =
CALENDAR(DATEVALUE(StartDate),DATEVALUE(EndDate))
Return
SELECTCOLUMNS(CalendarTable,
"Date",
[Date],
"Year",
YEAR([Date]),
"Date value",
VALUE(
FORMAT(
[Date],
"YYYYMMDD"
)
)
,
"YearMonth",
VALUE(
FORMAT(
[Date],
"YYYYMM"
)
)
,
"Quarter",
QUARTER([Date]),
"Month No",
MONTH([Date]),
"Month Name",
UPPER(
LEFT(
FORMAT([Date],"MMMM",Locale),
1
)
)&
MID(
FORMAT([Date],"MMMM",Locale),
2,
20
),
"Day",
DAY([Date]),
"Week No",
WEEKNUM([Date],21),
"Week Day No",
WEEKDAY([Date],2),
"Week Day Name",
UPPER(
LEFT(
FORMAT([Date],"DDDD",Locale),
1
)
)&
MID(
FORMAT([Date],"DDDD",Locale),
2,
20
),
"Day Type",
IF(
WEEKDAY([Date],2) > 5,
"Weekend",
"Work day"
),
"Month Start",
DATE(YEAR([Date]), MONTH([Date]), 1),
"Month End",
EOMONTH([Date], 0),
"Quarter Start",
DATE(YEAR([Date]), (QUARTER([Date]) - 1) * 3 + 1, 1),
"Quarter End",
EOMONTH(DATE(YEAR([Date]), QUARTER([Date]) * 3, 1), 0),
"Offset Year",
YEAR([Date]) - YEAR(TODAY()),
"Offset Quarter",
(
QUARTER([Date]) - QUARTER(TODAY())
)
+
(
YEAR([Date]) - YEAR(TODAY())
) * 4,
"Offset Month",
(
MONTH([Date]) - MONTH(TODAY())
)
+
(
YEAR([Date]) - YEAR(TODAY())
) * 12,
"Offset Week",
DATEDIFF([Date], TODAY(), WEEK),
"Offset Day",
DATEDIFF([Date], TODAY(), DAY)
)
```
How to set it up?
First of all, head into your Power BI Report in Power BI Desktop and go to TMDL view!
Next, paste in the TMDL Script in the editor (1) that should be roughly straight in front of your nose by now. Hit “Apply” (2)
Fear not the error message suddenly appearing on your screen! We just need to refresh the table once for it to actually generate the data. Simply click the “Refresh now” button.
Configuring the Date table
“But hey! I don’t want Swedish names!”
Not to worry. Simply scroll through the TMDL script until you see this. Change it to whatever locale you prefer and rerun the script.
“OK but I don’t want the date range being there by default!”
Change these variables to fit your needs better. The Date table is automatically created based on the current date when it’s refreshed and by default, it starts 5 years in the past and the full next year.
“Hm, yeah that’s seems easy enough but I want more control over the start and end date of the dates table”
Again, not a problem! Locate this portion of the script just below where you can set YearsPrior and YearsFuture. You can freely define how to calculate the StartDate and EndDate here by changing the DAX formula. One example might be to find the MIN and MAX value from a Facts table already in your data.
// Execute table calculations
VAR StartDate = DATE(YEAR(TODAY())-YearsPrior,1,1)
VAR EndDate = DATE(YEAR(TODAY())+YearsFuture,12,31)What’s the advantage of TMDL script compared to simply copy/paste the DAX code for a new table?
There are probably many more than these, but here’s my favorite reason!
You get all the small configurations done automatically in the script!
When I create the Date tables using only the DAX query, I always have to go into the data view and change the data type of the date from Date/time to Date and specify the format. The TMDL script handles this for me!
For numbers, Power BI have a tendency to be helpful by setting the default summarization to “Sum”. This is great. For actual numbers. This is a dimension and I always want each of these columns to be “Don’t Summarize”. I’ll let you guess if the TMDL script takes care of that too 😉
For names, like months and week days, I don’t want the default sorting. That would be alphabetically and rarely have my end users ever wanted to view the data like that. You know, having August before February because A comes before F.
Behold the magical parameters that makes the above possible. If you have a different table that you want to use across reports, you can use these to specifically summarize by sum, avg or other.
What are the improvements in V2.0 you spoke of earlier?
If you’ve read this far, I salute you and will reward you with the answer! Apart from the stuff above, which in themselves are improvements too, I’ve also added some new fields and default Hierarchies (thanks TMDL for making this possible too).
These fields are added to the date table now so it’s easier to make measures that depend on the start or end of months and quarters for example.
Some facts table doesn’t have the actual date, only the year and month as a YearMonth value and it’s typically the representative number of that date. This is included as YearMonth AND the full Date value as a number. I have stumbled upon facts tables that have this key for dates instead of the actual dates and now you can relate it to you date calendar too! Congrats!
“Looks nice. What about those Hierarchies?”
Of you mean these bad boys? There’s a bunch of them for your convenience. If you just want the year and month, grab that instead of the built in Date hierarchy before removing day and quarter. You can also choose between month names and numbers, you have weeks in there and finally I’ve added the Month Name Year! It might look a little bit odd and no, it’s not just the American way to write dates. It has a really great value for comparing information between years in the same month! It’s fast, even though I would lose sleep if I didn’t also recommend you to set Month as X axis and Year as Legend for even clearer comparisons.
Thanks for making it all the way here! Take care!
Cheers
About The Author
