How to calculate Current Fisacl Year Value


#1

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:


Nov1Today

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”.
DataRelationship

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.


#2

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

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

image


#3

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.

CurrentFY = CALCULATE( SELECTEDVALUE( Dates[FISCAL YEAR] ),
                    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.


#4

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.


#5

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


#6

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.


#7

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.


#8

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.

Thanks
Sam