Calculated column for FY error in last date

[Anonymous]

Hi all

I got my custom dateKey table that is created in DAX with the following function

_dateKey = CALENDAR(DATE(2018,1,1),DATE(2021,12,31))

In that table I added calculated columns for Year, Month, week, month order and FY.

so far so good. My calculated column for FY is the following:

FY = IF(_dateKey[Date]<DATE(_dateKey[Year],4,1),VALUE(FORMAT(_dateKey[Date],"YY")),VALUE(FORMAT(_dateKey[Date]+1,"YY"))+1)

The expected result is that the FY value is the same of the current year (in 2 digit format "YY"). On April 1st starts the next FY, meaning that after March 31st the FY value is the current year + 1. This is worked perfectly but the last value of my dateKey wich is December 31st of 2021 gets FY value 23, seems like a bug for me as this just happends in this date and not in previous December 31st...

any clue on what's happening?

Cheers.

Pepe

[HotChilli · Accepted solution]

Looks like the code adds 1 day to the date, which for the 31st of Dec pushes into the next year (1,1,2022), then extract the 22 then adds 1.

So that makes 23.