Many blogposts has been written about how you can create a generic date table in your Power Pivot / Power BI datamodels.
Here is a few examples
But my challenge with these has always been that I had to specify from date and to date either in a worksheet, text file or directly in the Query Editor.
Normally your fact table actually contains the max and min date that you actually want your date table to contain – so in the last model I created I decided to see if I could make the from and to date dynamic based on the max and min date in the fact table.
By using a bit of M magic functions it turned out to be very easy.
In my example I load the FactInternetSales table from the AdventureWorks database
Now as we can see in the query editor we have a column containing the Order date and the min and max of that column should be used to generate the table of dates.
Convert the OrderDate to Date instead of DateTime
Then add a blank query via the New Source button
In the formula bar you enter the following formula
And you get a list of dates – 🙂
Now we can add columns with the date information we want in our date tableby using the add Column Date – From Date & Time
The date table now depends on the Query FactInternetSales – and with the September update of the Power BI Desktop we can see that via the Query dependencies window
Now Power Query apparently knows that it has to update FactInternetSales before it updates the Date table, so when our facttable is updated the Dates get updates afterwards.
You could by modifying the expression to get the list of dates calculate the first day of the min year and last date of the max year etc. – all this depends on how complete you want the datetable.
Power Query is AWESOME J