Dynamically updating the End Date each year in the Dates Query

[GJ217]

Hi,

Does anyone know how to change the [M] code to dynamically update for each Calendar and Fiscal Year?

The fiscal year ends on the 31st March each year.

An example of both would be great then I an update the approiate Date table.

= #"Dates Query"(#date(2020, 4, 1), #date(2023, 12, 31), null, null, null)

[Ashish_Mathur]

Hi,

It is easier to create a Calendar Table in the Date Model (rather than in the Query Editor).  The following formula will create a list single table of Dates (from the first date in the Data Table to the last date in the Data Table)

Calendar = calendar(min(Calendar[Date]),max(Calendar[Date]))

You can create calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number column.

Hope this helps.

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

[GJ217]

Hi @lbendlin 

I have a fiscal year calendar and I need the end date to update to the next fiscal year when we reach 31/03/2025 without me updating it manually as I will need to do in next year in the code below.

let
    Source = #"Dates Query"(#date(2020, 4, 1), #date(2024, 03, 31), 4, null, null),

After watching a YT video I created two blank queries the first using the Termination Date column, however found that this would not work for a Calendar Date table as I need the EndOf Year to be the current calendar EOY 31/12/2023 to update to 31/12/2024 in Jan 24, whereas the List is bringing back the last date in the data set which in the case is 01/02/2024 hence why I'm getting 31/12/2024 instead of 31/12/2023.

= List.Max(PersonDetails[TERMINATION DATE])

= Date.EndOfYear(LastTerminationDate)

let
    Source = #"Dates Query"(#date(2020, 4, 1), EndOfYear, 4, null, null),

The above steps do not work at all for Fiscal Year EndOfYear so would be open to any other suggestions in how to dynamically update EndOfYear for both Calendar and Fiscal year date tables in power query.