10-11-2017 08:26 PM
You just need to change the first line from
the first number value in that formula needs to change.
<7 is for a financial year that goes from 1st July to 30th June.
Fiscal Year = IF(Month(DetailedDates[Date]) < 7, Value(FORMAT(DetailedDates[Date], "YYYY")), VALUE(FORMAT(DetailedDates[Date], "YYYY")) +1)
so to get it from April to March you just need to change the "< 7" in the above to "< 4"
So the financial year starts in the 4th month.
11-10-2017 02:23 AM - edited 11-10-2017 02:25 AM
Thank you for posting this. It did give me the idea on how to work this out. However, I think I have made it even more dynamic. My FY is April - March. Here is the formula that I used which works as well but it is dynamic:
FY = IF(FY_Calendar[MonthNo]<=3,"FY"&RIGHT(FY_Calendar[Year]-1,2),"FY"&RIGHT(FY_Calendar[Year],2))
04-17-2018 07:41 AM
In case anyone else needs the PowerQuery formula for this:
if Date.Month([Month]) <=6
else Text.From(Date.Year([Month]) +1 )