I’ll show you how to set up dynamic start dates and end dates using Power BI’s power query date tables.
If you’ve been following this series on different Time Intelligence subjects, you already know how to create a date table using power query functions. You might have also read about how to change the end date for that table. You may watch the full video of this tutorial at the bottom of this blog.
This time, however, I’ll show you a few alternatives.
The Power Query Date Table
I’m going to start off by heading to the power query date table function. It shows four date parameters – a required StartDate, a required EndDate, an optional FYStartMonth and an optional holiday list.
Note that for every query you invoke, the corresponding query function will show up here.
You’ll also see the date parameters in the same order they were listed earlier. So this shows the StartDate, the EndDate and the fiscal year. It also shows null for the holiday list.
Now that you’ve seen the parts of the date table function, I’ll show you the different query functions you can use.
Power Query Date Table Functions
If I go into my Sales table, which is also my fact table, it shows an OrderDate column.
If I click on the dropdown beside OrderDate, it will show that the first date in this calendar is the 24th of September 2017.
So I’m going to extract that data by creating a new blank query.
I’ll use the List.Min function.
Looking at the description, it shows that List.Min returns the minimum item in the list.
For this function, I’m going to reference the Sales table and the OrderDate column.
Once I hit enter, it gives me the first date, which is the 24th of September, 2017.
I’ll rename this query and call it FirstSale.
But making the calendar start on the 24th of September probably doesn’t make sense. So I’ll turn this back into the start of the year.
To do that, I’ll use a blank query again. This time, I’ll use the function Date.StartOfYear.
Date.StartOfYear returns the first value of a given year for a date, datetime or datetimezone value.
For my date value, I’m going to use FirstSale.
Once I hit enter, it now shows the first of January, 2017.
I’ll rename this query too, and call it StartOfYear.
But there’s another way to get exactly the same value. That’s by using the intrinsic date.
So I’m going to create a new query again and use #date, which is the intrinsic date value.
For #date, I need a dynamic value. So I’ll use Date.Year and reference FirstSale. I would also use the 1st of January, which is represented by the two 1’s on the measure.
I’ll name this fxStartDate.
Now, I’m going to go back to the date table query. Now, I can change the StartDate parameter.
It doesn’t really matter if I select StartOfYear or fxStartDate since they’ll yield the same result.
I’m just going to use the fxStartDate so I can put a proper value here.
Now, my calendar starts from January 1st, 2017.
Of course, I can do the exact same thing for the EndDate. I just need a blank query again, but this time, I’ll be using the function List.Max. I’m also going to reference Sales and the OrderDate column.
This now shows the last date from the Sales table, which is the 24th of April, 2020.
I’ll double check if that’s correct by sorting this column in a descending manner. Surely enough, the column does show April 24 2020 as the final date.
I’ll rename this query LastSale.
Again, I can do exactly the same thing for the end of the year just as I did for the StartOfYear.
On a new query, I’ll use Date.EndOfYear and reference LastSale.
I’ll call this EndOfYear.
Remember how I used the intrinsic date for the StartOfYear? I can also do that to return the 31st of December of any given year. However, you also have to think about the possibility of the data spilling over into the new year once it reaches that end date.
This is how you can deal with that.
I’ll use Date.AddYears and reference EndOfYear.
For the number of years, I’m just going to add 1.
Sure enough, the date now shows 2021.
I’ll call this EndOfNextYear.
I’m now going to replace the EndDate on my date table.
I’ll use EndOfNextYear to replace that.
Double checking my years, it now runs to 2021.
To save and apply all of the changes I made, I’m going to click on Close & Apply.
Other Date Functions
Go to docs.microsoft.com and look up Power Query M formula language.
Once that shows up, look for the Date functions overview.
This will show you all the other date functions that you can use when working inside power query date tables.
***** Related Links *****
Build A Comprehensive Date Table In Power BI Really Fast
Time Comparison For Non Standard Date Tables In Power BI
Adding Workday And Weekend Numbers Into Your Date Table: Time Intelligence Technique In Power BI
Conclusion
There are many functions you can play with when using power query date tables, which is why I suggest looking through each one of them.
Start creating blank queries and using them to see what happens. This is the best way to find out how each query function can help you out in the scenarios you might face in the future.
Honestly, the power query is an amazing tool that could give you great insights, especially when dates are involved. Try to check out other posts about time intelligence in this blog to see what other techniques you can apply.
All the best,
Melissa
