Fix calendar to calculate YTD and YTD% correctly when leap year

[phorfanos]

Hi, below you may find the Power Query generated Calendar Table: 

let
StartDate = #date(2017, 1, 1),
EndDate = Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow())),
CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
#"==SET PARAMETERS ABOVE==" = 1,
#"==Build Date Column==" = #"==SET PARAMETERS ABOVE==",
ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}),
#"==Add Calendar Columns==" = #"Changed Type to Date",
#"Added Calendar MonthNum" = Table.AddColumn(#"==Add Calendar Columns==", "MonthNum", each Date.Month([Date]), Int64.Type),
#"Added Month Name" = Table.AddColumn(#"Added Calendar MonthNum", "Month", each Text.Start(Date.MonthName([Date]),3), type text),
#"Added Calendar Quarter" = Table.AddColumn(#"Added Month Name", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date]))),
#"Added Calendar Year" = Table.AddColumn(#"Added Calendar Quarter", "Year", each Date.Year([Date]), Int64.Type),
#"Added CurMonthOffset" = Table.AddColumn(#"Added Calendar Year", "CurMonthOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate) ) * 12
+ Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),
#"Inserted Day of Year" = Table.AddColumn(#"Added CurMonthOffset", "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),
#"Added Flag_MTD" = Table.AddColumn(#"Added Flag_YTD", "Flag_MTD", each if Date.Day([Date]) <= Date.Day(CurrentDate)
then "MTD"
else null)

in
#"Added Flag_MTD"

There is an issue with the bolded lines where I create a Flag_YTD column used as a slicer in my reports. In the case of a leap year like 2024, when I want to compare the YTD for 2024 and 2023 (or for 2020 and 2019), YTD for the non-leap year 2023 counts one more day (as the 29th of February does not exist for 2023). Can you please help me modify the code to fix this? As I see it, I need to fix the Day of Year column to give one less row for the non-leap years when the current date refers to a leap year. Any ideas? 

[watts_jim]

= Table.AddColumn(#"Inserted Day of Year", "Flag_YTD",
each if ((Date.Year([Date])*10000) + (100 * Date.Month([Date])) + Date.Day([Date]) )
<= ((10000 * Date.Year(CurrentDate) ) + (100 * Date.Month(CurrentDate)) + Date.Day(CurrentDate) )
then "YTD" else null)

[phorfanos]

What I want is the Flag_YTD column to be based on the Day of Year. When non-leap years, the FLAG_YTD should go up to 74 as of today (that is, 15th March of 2023 or 15th March of 2022) or up to 74 for leap years (that is 15th March of 2024 or 15th March of 2020)...

[phorfanos · Accepted solution]

Guys I think I solved it! I just splitted the problematic part in 3 steps and got what I wanted:
Instead of using this 
#"Added Flag_YTD" = Table.AddColumn(#"Inserted Day of Year", "Flag_YTD", each if Date.DayOfYear([Date]) <= Date.DayOfYear(CurrentDate)
then "YTD"
else null),

I replaced it with the following: 

#"Added Flag_YTD_Leap" = Table.AddColumn(#"Inserted Day of Year", "Flag_YTD_Leap", each
if Date.IsLeapYear(Date.Year([Date])) then
if Date.DayOfYear([Date]) <= Date.DayOfYear(CurrentDate) then "YTD" else null
else null
),

#"Added Flag_YTD_NonLeap" = Table.AddColumn(#"Added Flag_YTD_Leap", "Flag_YTD_NonLeap", each
if not Date.IsLeapYear(Date.Year([Date])) then
if Date.DayOfYear([Date]) <= Date.DayOfYear(CurrentDate) - 1 then "YTD" else null
else null
),

#"Merged Flag_YTD" = Table.AddColumn(#"Added Flag_YTD_NonLeap", "Flag_YTD", each if [Flag_YTD_Leap] = "YTD" or [Flag_YTD_NonLeap] = "YTD" then "YTD" else null)

[phorfanos]

thank you @watts_jim , but this produces an error for the Flag_YTD column...

[watts_jim]

If you multiply the month number by 12 and add the day of the month, you can compare and handle Leap Years:

#"Added Flag_YTD" = Table.AddColumn(#"Inserted Day of Year", "Flag_YTD", each if (12 * Date.Month([Date]) + Date.Day([Date]) ) <= (12 * Date.Month([CurrentDate]) + Date.Day([CurrentDate]) ) then "YTD" else null),