cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elliotdixon Member
Member

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

Jawed Regular Visitor
Regular Visitor

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

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

 

 

KDford Visitor
Visitor

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 )
})
duyarra Frequent Visitor
Frequent Visitor

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

Just what I needed, thanx!

Wang1234 Frequent Visitor
Frequent Visitor

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

Fiscal Year Quarter Cut Off works well and it applies to all no matter when the Fiscal Year start, April or October.

 

If you are looking for Fiscal Year CutOff to dynamically display measures of "Current Fiscal Year", "Previous Fiscal Year" and etc.,  Here are the steps and DAX will work.   Please comment if this works, Thank you.

 

Note: Fiscal Year Start from April 1st in this example.

Fiscal Year Number= If(
Month(Date[date])<= 3,
Year(Date[date])-1,
Year(Date[date])
)
Current Fiscal Year Number = If( 
   				Month(today())<= 3, 
Year(today())-1,
Year(today())
)
Fiscal Year CutOff = Date[Fiscal Year Number]-Date[Current Fiscal Year Number]
Wang1234 Frequent Visitor
Frequent Visitor

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

"Fiscal Year CutOff" = 0, Current Fiscal Year

"Fiscal Year CutOff" = -1, Previous Fiscal Year

...

Vaulttechie Occasional Visitor
Occasional Visitor

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

My Fiscal year runs from April 1 to March 31.  I found this message very useful in solving my needs.  Here is what I did

 

Step 1.

Create 2 columns, Month and Year  (for my purposes I used a column called Received Date so my columns are titled the same

1.  Received MONTH = tblAssessments[ReceivedDate].[MonthNo]

2.  Received YEAR = tblAssessments[ReceivedDate].[Year]

 

Step 2. 

Create a Fiscal Year Column without having to have YEAR be a calculation of the IF statement

- (Note: Concatenate is limited 2 items in Power BI, I would have preferred to add a "/" between the years

 

Fiscal Year = IF(tblAssessments[Received MONTH]>=4,CONCATENATE(tblAssessments[Received YEAR],tblAssessments[Received YEAR]+1),CONCATENATE(tblAssessments[Received YEAR]-1,tblAssessments[Received YEAR]))
 
example: This code would allow April 2011 to December 2011 to be considered in the fiscal year 2011/2012 and January 2012 to March 2012 to be considered in the fiscal year 2011/2012 

 

KristinS Occasional Visitor
Occasional Visitor

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

This thread was immenselty helpful.  Thank you!

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 60 members 1,332 guests
Please welcome our newest community members: