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, paste 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.
- Date table function query name (the query that was invoked)
- Startdate (required parameter of type date)
- Enddate (required parameter of type date)
- Fiscal Year Startmonth (optional parameter of type number)
- Holiday List (optional parameter of type list)
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…
.
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
#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 
.
Would you rather see a video?
