Issue with YTD flag in the case of a leap year

[phorfanos]

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? 

[lbendlin]

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.

[phorfanos]

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? 

[lbendlin]

It's a DAX function, not Power Query.

[phorfanos]

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.

[lbendlin]

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