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

No comments

I’ll show you how to set up dynamic start and end dates when you’re using 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.

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 parameters – a required StartDate, a required EndDate, an optional FYStartMonth and an optional holiday list.

power query date

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

power query date

You’ll also see the 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

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

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.

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

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

This image has an empty alt attribute; its file name is membership-banner-3-e1539659838128.png

***** 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

***** Related Course Modules *****
Time Intelligence Calculations
Power Query Series
Best Practices & Feature Reviews

***** Related Support Forum Posts *****
Extended Date Table (Power Query M Function)
Creating A Dynamic Start-/EndDate For The Date Table
Custom Query For The Date Table Not Working In Power BI
For more power query date table support queries to review see here…

Enterprise DNA Events


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.