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

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
Jawed Helper II
Helper II

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

 

 

Anonymous
Not applicable

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

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

Just what I needed, thanx!

Wang1234
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

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
New Member

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
New Member

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

This thread was immenselty helpful.  Thank you!

MarkPalmberg Advocate III
Advocate III

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

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

Dlan1
Regular Visitor

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

Hello, 

 

I've tried to use the following section to create a Fiscal Year column:

 

Fiscal Year = 

IF(AND([Year] =2017,[Month]>3),"FY17/18",

IF(AND([Year] =2018,[Month]<4),"FY17/18",

IF(AND([Year] =2018,[Month]>3),"FY18/19",

IF(AND([Year] =2019,[Month]<4),"FY18/19",

IF(AND([Year] =2019,[Month]>3),"FY19/20",

IF(AND([Year] =2020,[Month]<4),"FY19/20"," ")))))))

 

I've tried to do this via creating a custom column and as a measure. Neither is working. 

 

The error when creating a custom column is "Token RightParen Expected". However I have all the right parenthesies I need'; adding or subtracting them makes no difference to the error. 

 

The error in a measure relates to Month and Year not being recognised. 

 

Is anyone able to help me?

 

My aim is to end up with a silcer that has three check boxes in it. The first filters to FY17/18, the next to FY18/19, the third to FY19/20.

 

If anyone can help me I'd really appreciate it. Thanks

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors