Setting Up A Dynamic StartDate And EndDate For Power Query Date Tables

by | Power BI

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.

power query date, start date and end date in power bi screenshot

Note that for every query you invoke, the corresponding query function will show up here.

power query date, power query date parameter screenshot

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.

power query date, date parameter in power bi screenshot

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.

power query date, date parameter power bi screenshot

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.

power query date

Looking at the description, it shows that List.Min returns the minimum item in the list.

power query date

For this function, I’m going to reference the Sales table and the OrderDate column.

power query date

Once I hit enter, it gives me the first date, which is the 24th of September, 2017.

power query date

I’ll rename this query and call it FirstSale.

power query date

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.

power query date

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.

power query date

For the number of years, I’m just going to add 1.

power query date

Sure enough, the date now shows 2021.

power query date

I’ll call this EndOfNextYear.

I’m now going to replace the EndDate on my date table.

power query date

I’ll use EndOfNextYear to replace that.

power query date

Double checking my years, it now runs to 2021.

power query date

To save and apply all of the changes I made, I’m going to click on Close & Apply.

power query date

Other Date Functions

Go to docs.microsoft.com and look up Power Query M formula language.

power query date

Once that shows up, look for the Date functions overview.

power query date

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

[youtube https://youtu.be/iveCss_hzDo?rel=0&w=784&h=441]

Related Posts

Using the DISTINCT Function Effectively in DAX

DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.