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

 

 

Highlighted
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!

MarkPalmberg Regular Visitor
Regular Visitor

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

This is a must-have reference for date column variations.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,013)