topic Re: Issue with YTD flag in the case of a leap year in DAX Commands and Tips

Issue with YTD flag in the case of a leap year

phorfanos — Mon, 04 Mar 2024 11:36:36 GMT

Hi,

A part of my code in my advance editor that builds my calendar table is the following:

...
#"Inserted Day of Year" = Table.AddColumn(#"==YTD QTD MTD Columns", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Added Flag_YTD" = Table.AddColumn(#"Inserted Day of Year", "Flag_YTD", each if Date.DayOfYear([Date]) <= Date.DayOfYear(CurrentDate)
then "YTD"
...
else null)

and currentdate is defined as

CurrentDate = DateTime.Date(DateTime.FixedLocalNow())

If we consider that the date is 4 March 2024, then the YTD flag is true in the case of leap years (i.e. 2024 or 2020) but in the case of standard years (2023, 2022, 2021) it adds also the next day, 5th of March in the calculations of i.e. sales. So I actually do not compare apples to apples since for 2024 the total sales for March incude 1st, 2nd, 3rd and 4th of March but for year 2023 the total sales included 1st, 2nd, 3rd, 4th and 5th of March in the calculation.

How can we solve this?

Re: Issue with YTD flag in the case of a leap year

lbendlin — Mon, 04 Mar 2024 19:10:13 GMT

EDATE([date],-12)

You wouldn't believe how much effort went into making that innocent looking function work reliably. It is truly an underappreciated gem.

Re: Issue with YTD flag in the case of a leap year

phorfanos — Tue, 05 Mar 2024 07:50:33 GMT

Dear lbendlin thank you for your prompt reply. Could you please be more specific? How will you modify my code? Can I insert the EDATE function in the advance editor?

Re: Issue with YTD flag in the case of a leap year

lbendlin — Tue, 05 Mar 2024 12:54:32 GMT

It's a DAX function, not Power Query.

Re: Issue with YTD flag in the case of a leap year

phorfanos — Tue, 05 Mar 2024 13:03:08 GMT

I agree, this is why I asked how we could modify the code in the advanced editor. My objective is to create a column in the Calendar table with the name "flag_YTD" and the use it as a slicer in the reports.

Re: Issue with YTD flag in the case of a leap year

lbendlin — Tue, 05 Mar 2024 13:20:33 GMT

As far as I know there is no equivalent function for that in Power Query.