The aim to to create a column that shows whether a particular date falls in the respective dates financial year to date;
Financial year = 1st April - 31st March
Based on todays date of 19th May;
April 1st = 1st day of Financial Year so column would say FYTD as falls between 1st April - 19th May (FY day 1 - 49).
20th May - 50th day of Financial Year so column would say null as doesnt fall between 1st April - 19th May (FY day 1 - 49).
The above should work for any date within any year so;
May 19 2017 = FYTD
May 20 2023 = NULL
A online calendar table had the following DAX for YTD Flag = If Date.DayOfYear([Date]) <= Date.DayOfYear(CurrentDate)
then "YTD"
else null
I have tried, but cannot figure out how to recreate for financial year, as the one above lists Jan 1st - March 31st values as YTD, whereas I would like FYTD to be April 1 - March 31. In that sense, Jan 1 - Mar 31 wouldnt show as FYTD (based on today being May 19th).
I looked online and created columns for day of year and fiscal year day of year, thinking that I could work out the difference between them to get my answer but I doesnt work due to the calendar year element making Dec 31 day 365 and Jan 1 day 1.
Day Of Year = Date.DayOfYear([Date]) - output is range from day 91-365 (April 1st - Dec 31) and then 1 - 90 (Jan 1st - Mar 30).
Fiscal Year Day Of Year = if ([Day of Year] - 90) <= 0 then (([Day of Year]-1) + 366) - 90 else ([Day of Year] - 90)
Any help that anyone can provide on this would be hugely appreciated as I am stumped.
