How to calculate Current Fisacl Year Value


Hello Sam,

I would like to calculate a new column(or measurement) called “CurrentFiscalYearCutOff” to indicate the current fiscal year, previous fiscal year, Previous-2 fiscal year.
CurrentFiscalYearCutoff=0, current fiscal year
CurrentFiscalYearCutoff=-1, previous fiscal year
CurrentFiscalYearCutOff=-2, previous -2 fiscal year
For information the Fiscal year starts on November 1st and ended on Oct 31.

Pls find below two examples with more details:


One more thing, the fact table (cases) and Dates table have inactive relationships but I am not sure whether this matter when we calculate the “CurrentFiscalYearCutOff”.

I would like to calculate the same for 'CurrentFiscalQuarterCutoff" to identify the current fiscal quarter, previous fiscal quarter and previous -2 fiscal quarter. Your help will be appreciated. Thank you.


I don’t believe this would be too difficult.

You would just need to, within a calculated column, work out what financial year you are currently in and then subtract the financial year from each row.

Something like this.

Fin. Year Cut Off = 
VAR CurrentFinYr = VALUE( CALCULATE( MAX( Dates[Year] ), Dates[Date] = TODAY() ) )

IF( CurrentFinYr = Dates[Financial Year], 0, Dates[Financial Year] - CurrentFinYr )



Thanks, Sam and this works and a much better/simpler solution. I tested it and made a small change to the first DAX.

CurrentFinYr= VALUE( CALCULATE( MAX( Dates[FISCAL YEAR] ), Dates[Date] = TODAY() ))

I watched your another Post with similar topic and here is my solution.

                    FILTER( ALL( Dates ), Dates[Date] = TODAY() ) )
CurrFY_Cutoff = if ('Dates'[FISCAL YEAR]=Dates[CurrentFY],"0",( 
                              if ('Dates'[FISCAL YEAR]=Dates[CurrentFY]-1, "-1",
                                     if ('Dates'[FISCAL YEAR]=Dates[CurrentFY]-2, "-2", 
                                          if ('Dates'[FISCAL YEAR]=Dates[CurrentFY]-3, "-3", 
                                               if ('Dates'[FISCAL YEAR]=Dates[CurrentFY]-4, "-4", 
                                                    if ('Dates'[FISCAL YEAR]=Dates[CurrentFY]+1, "+1",blank()

I like your solution better since it is simple and efficient. Thank you.


One More thing, I still cannot figure out how to calculate the same for Current Fiscal Quarter Cut Off and it seems complex and have no clue how to do it. thank you.


Yes definitely go with the most simplest solution at all times if you can. I always personally do this.


Can you show me an example of what this needs to look like?

I’m sure it will be a small variation on the previous solution.


Hello Sam,

The expected result is like this:
if Dates(Fiscal Quarter)=Current FY Quarter, then “Current FY Quarter Cutoff”=0,
if Dates (Fiscal Quarter) = Previous FY Quarter then “Current FY Quarter Cutoff” = “-1”
If Dates (Fiscal Quarter) = Previous 2 FY Quarter then “Current FY Quarter Cutoff”="-2".
I first calculate the “Current FY Quarter” (refer to below DAX)

    CurrentFY Quarter = 
CALCULATE( SELECTEDVALUE( Dates[Fiscal Quarter] ),
                        FILTER( ALL( Dates ), Dates[Date] = TODAY() ) )

Then I use this DAX to calculate “Current FY Quarter Cutoff” but have error:

        CurrentFY Quarter Cutoff = 
        if( Dates[Fiscal Quarter]=Dates[CurrentFY Quarter],0,(Dates[Fiscal Quarter]-Dates[CurrentFY Quarter]))

I got below error:

I am wondering how to calculate the difference of current Fiscal Quarter “19-Q1” and previous Fiscal Quarter “18-Q4” and get “-1” as expected? Thank you.


So to me if looks like your to use text columns to return numerical values which is never going to work.

You need to create a yearly column for the financial year and work with that.

You see in my example above that is exactly what I have done.

So look to extract the year into a separate column and then run the logic exactly as I showed above.

Should be relatively straight forward if you follow the answer above I believe.