Hi all, in this post, let us make a dynamic date dimension.

If you followed me every step of the way, you should already have this on your table. If not, click on the blank query and paste the following code to speed up and read about the previous post here, and download the sample file below.

let
    Source = List.Dates(#date(2020,1,1), 366, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
       
in
    #"Changed Type"

Now that we are on the same page, let us begin!!!
I hope you still remember the three main requirements for a date table. I know I have said this over and over, but once again I will reiterate this that to have a date table, we need StartDate, EndDate, and Duration.

Now with this in mind, let us begin. First, we need to find the StartDate. In this case, it is 01/01/2020.

Click on the function icon (fx) and insert a new step.

By default, you should see that this new step is called Custom 1 and that it is referencing the previous step, as shown below. In my case, it is referencing the step called Inserted Day of Year; for you, it could be different. But the main point here is whenever we add a new step, then by default, it will always refer to the immediate prior step.

Delete everything including the ‘=’ sign and type in 01/01/2020 and while you are at it, rename this step (right-click on the Custom 1) to StartDate.

This will fetch us the StartDate. Since now we have StartDate, go ahead and replace #date(2020, 1, 1) in the source step with StartDate. 

Now we need a count of days. For this let us dynamically get the count of days from 01/01/2020 till today. 

Create a new step and input this DateTime.Date(DateTime.LocalNow()) in this formula bar and rename this step as EndDate.

This nested function first fetches the local/current date in DateTime format, then we extract just the date component from it. 

Insert another step, and this time we will invoke Duration. Days function and wrap it around EndDate minus StartDate. This time we are subtracting the StartDate from EndDate and fetching the number of days from it. (If you want to include today, then we must add 1 to it so we can have today counted as well). Let us rename this new step as Count.

At this point, we have StartDate and Count. Let us navigate back to the source step and replace the hardcoded 366 with Count. 

Now insert another step and reference it back to the Changed Type step in the applied steps as shown below. By default, every new step references the immediate prior step, but we want to reference another step hence, the need to manually tell M query where to look for. 

Once we have the above step, all that is needed is to extract relevant date parts as we did in the previous blog. 

At this point, our Date Table is ready. We can use this code and just by changing the StartDate we can generate all the dates till today. I changed my StartDate to 01/01/2010 and I can now see all the dates from 01/01/2010 till today.

If you noticed, we had to enter the StartDate manually, but what if we wanted to use the earliest date present in our model. Well, it is straightforward to do so.

In our sample, we have an Orders table. Let us say that we wanted to use the first Order Date as my StartDate.
We can call another List function called List. Min and pass that to our source. Let us see that in action.

For the sake of simplicity and comparison, I am going to insert another step right after StartDate. Since we know that every new step in M Query references the immediate prior step, inserting a new step right after StartDate will not break our code.

Insert a new step after StartDate and rename as MinDate.

• Since we want the earliest date from Orders Table, call List.Min function on the Order Date column.
• For the MinDate step, insert this ” = List.Min((Orders[Order Date]))”. This will look for the earliest date in the column called Order Date in the table called Orders. 

Once we have this, go back to the source and replace StartDate with MinDate. 

At this point, you should have something like below. Here the date table starts from 01/01/2015 which is the first order date in the Orders table. 

Lets us now say, that we wanted the EndDate to be the last ship date. This is also equally easy. Follow these steps and you will have a true dynamic date table whose dates are based on the dates in your model.

1. Insert a new step after EndDate and rename it as MaxDate.
2.  Since we want the latest date from Orders Table, call List. Max function on the Ship Date column.
3. For the MaxDate step, insert this ” = List.Max((Orders[Ship Date]))”. This will look for the latest date in the column called Ship Date in the table called Orders. 

At this point, you should have something like below. Here the date table ends on 07/07/2015 which is the second last ship date in the Orders table. To get the last ship date, just add 1 to the count step as follows. 

This way we can create a dynamic date table that fetches the StartDate and the EndDate using the data within our model.

I am including all the variations of the M code for easy reference.

 let
    Source = List.Dates(MinDate, Count, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    // We can manually choose the Start Date and the rest of calendar will populate based on this value
    StartDate = #date(2010, 1, 1),
    // this is taking the earliest date from Order Date column. 
    MinDate = List.Min((Orders[Order Date])),
    // this is taking end date as today's date
    EndDate = DateTime.Date(DateTime.LocalNow()),
    // this is taking the last Ship Date 
    MaxDate = List.Max((Orders[Ship Date])),
    Count = Duration.Days(MaxDate - MinDate) + 1,
    Custom1 = #"Changed Type",
    #"Inserted Year" = Table.AddColumn(Custom1, "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Week of Year", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Inserted Day of Year",{{"Date", Order.Descending}})
    
in
    #"Sorted Rows"

I hope you liked demystifying date dimension series and please do let em know you thoughts on this. If you have a request, please do not hesitate to reach out.

Till next time !!!