Creating a dynamic Start-/EndDate for the Date table

Hi everyone,

To make it easier to find here are some Power Query techniques to make the Date table Startdate and/or Enddate dynamic. If you want to give that a try, go to Power Query, select New Source, Blank Query, open the Advanced Editor, past in this Date table M code and Invoke that function.

A new query will appear in the Queries pane with the name ‘Invoked Function’ in the formula bar of that query’s Source step you can distinguish the following elements.

  1. Date table function query name (the query that was invoked)
  2. Startdate (required parameter of type date)
  3. Enddate (required parameter of type date)
  4. Fiscal Year Startmonth (optional parameter of type number)
  5. Holiday List (optional parameter of type list)

image

Both Start- (2) and Enddate (3) are now constructed using the #date intrinsic.
#date( year, month, day )

but any M expression returning a value of type date can be used of course. Here are some M expressions that are useful when setting up dynamic start- and enddates to your calendar.

M function Description
#date Creates a date value from year, month, and day.
#duration Creates a duration value from days, hour, minute, and second.
DateTime.FixedLocalNow Returns a datetime value set to the current date and time on the system. This value is fixed and will not change with successive calls, unlike DateTime.LocalNow, which may return different values over the course of execution of an expression.
Date.From Returns a date value from a value.
Date.Year Returns the year from a DateTime value.
Date.AddDays Returns a Date/DateTime/DateTimeZone value with the day portion incremented by the number of days provided. It also handles incrementing the month and year potions of the value as appropriate.
Date.StartOfMonth Returns a DateTime value representing the start of the month.
Date.EndOfMonth Returns a DateTime value for the end of the month.
Date.AddMonths Returns a DateTime value with the month portion incremented by n months.
Date.StartOfYear Returns a DateTime value representing the start of the year.
Date.EndOfYear Returns a DateTime value for the end of the year.
Date.AddYears Returns a DateTime value with the year portion incremented by n years.
List.Min Returns the minimum item in a list, or the optional default value if the list is empty.
List.Max Returns the maximum item in a list, or the optional default value if the list is empty.

For more M functions visit the online Power Query M function reference, link below…
https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference
.

Passing an expression or a query?
Let’s assume we know the Startdate and that will never change but we want to create a dynamic Enddate for the Date table. In that case we can change the #date intrinsic (3) in the Source step.

#date( Date.Year( DateTime.FixedLocalNow()) +1, 12, 31 )
This M expression for example will get the Year from the current date and add 1, thus always returning December 31st next year.

Passing an M expression can sometimes make the M code harder to read/understand so instead you could also create a New from Blank Query rename that query MaxYear and enter the following M expression = Date.Year( DateTime.FixedLocalNow()) +1
image

#date( MaxYear, 12, 31 )
Will now get you exactly the same result but the M code is much easier to read. Performance wise there is no difference between the two. Personally I like this method because it’s clean and easy to debug.
.

Examples for Dynamic Start- and Enddates

M code example Result
List.Min( QueryName[DateColumn]) First date from selected List
#date( Date.Year( List.Min( QueryName[DateColumn])), 1, 1) Start of Year First date
Date.StartOfYear( List.Min( QueryName[DateColumn] )) Start of Year First date, single List
Date.StartOfYear( List.Min( {List.Min( QueryName[DateColumn] ), List.Min( QueryName[DateColumn] ), List.Min( QueryName[DateColumn] )} )) Start of Year First date, multiple Lists
List.Max( QueryName[DateColumn]) Last date from selected List
#date(Date.Year(DateTime.FixedLocalNow())+1, 12, 31) End of next Year
Date.AddYears( Date.EndOfYear( Date.From( DateTime.FixedLocalNow())),1) End of next Year
Date.EndOfYear( List.Max( QueryName[DateColumn] )) End of Year Last date, single List
Date.EndOfYear( List.Max( {List.Max( QueryName[DateColumn] ), List.Max( QueryName[DateColumn] ), List.Max( QueryName[DateColumn] )} )) End of Year Last date, multiple Lists

Note. Please know that referencing multiple Lists is not ideal and could become costly. Combining the distinct values from one or several Lists inside a List.Buffer and/or other optimization techniques should be explored.

.

You can let me know you’ve appreciated this post by giving it a :heart:

.

Would you rather see a video?

8 Likes

adding some search terms and closing this thread.
dynamic startdate, start date, enddate, end date calendar date table function. Power Query M

@Melissa,

Fantastic post, perfectly explained. Thanks so much for taking the time to put all of this together. The M Showcase section on the forum is quickly becoming my go-to Power Query reference site.

standing ovation

  • Brian

P.S. I used this to quickly resolve the circular reference problem I was having with my dynamic date table.

2 Likes

@Melissa,

Quick question - per your recommended approach above, I’ve set up the brief query below to calculate the max date for my dynamic date table. However, before I feed this in to the date table query, how can I test/evaluate it to make sure it’s returning the date value I think it should be?

Thanks!

– Brian

Hi Brian,

So I replicated what you where doing by first creating a query called CurrentDate that contains this M code:
= DateTime.FixedLocalNow()

CurrentDate result = 2020-05-06T19:28:16.1042913

Second query called MaxDate contains this M code:
= if Date.Month(CurrentDate) >9 then #date(Date.Year(CurrentDate)+1, 9, 30) else #date(Date.Year(CurrentDate), 9, 30)

MaxDate result = 30-9-2020

The results can be seen in the preview pane when the query is selected.

1 Like