Reply
Member
Posts: 139
Registered: ‎09-27-2015

Re: DAX Formulas for Fiscal Year, Fiscal Quarter and Month Short Name

Hi @rkalantri

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.

 

Rgds

Highlighted
Regular Visitor
Posts: 36
Registered: ‎11-02-2017

Re: DAX Formulas for Fiscal Year, Fiscal Quarter and Month Short Name

[ Edited ]

Hi there,

 

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))

 

 

Visitor
Posts: 1
Registered: ‎04-17-2018

Re: DAX Formulas for Fiscal Year, Fiscal Quarter and Month Short Name

In case anyone else needs the PowerQuery formula for this:

 

Text.Combine({"FY",
if Date.Month([Month]) <=6
then Text.From(Date.Year([Month]))
else Text.From(Date.Year([Month]) +1 )
})
Frequent Visitor
Posts: 2
Registered: ‎04-13-2018

Re: DAX Formulas for Fiscal Year, Fiscal Quarter and Month Short Name

Just what I needed, thanx!