How to calculate Current Fisacl Year Value

Hello Fellow Power BI Users -

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.

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

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.

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.

Thanks
Sam

  • Background information:
    Fiscal Year started November 1 and ended October 31.

  • The goal is to create “Current Fiscal Quarter Cut Off” :
    For example,
    0=Current Fiscal Quarter,
    -1= Immediately Past Previous Fiscal Quarter,
    -2=Previous 2 Fiscal Quarter,
    -3 = Previous 3 Fiscal Quarter etc.

    -N = Previous N Fiscal Quarter

  • Steps & questions:
    Step 1 to calculate the Current Fiscal Quarter - (Success)

           CurrentFYQuarter = CALCULATE( SELECTEDVALUE( Dates[FISCAL QUARTER] ),
                              FILTER( ALL( Dates ), Dates[Date] = TODAY() ) )
    

    Step 1 Result is good

Step 2: calculate “Current Fiscal Quarter Cut Off”

CurrentFYQuarterCutOff = if(Dates[CurrentFYQuarter]=Dates[FISCAL QUARTER],0, DATEDIFF(today(),Dates[Date],QUARTER))

Step 2 result is not 100% correct

Step 2 Issue: DATEDIFF can only calculate the number of “Calendar Quarters” between two dates. I wonder how to calculat the number of “Fiscal Quarter” between two dates.

For example, If today =November 13 2018 and Dates (Date)= Oct 31 2018, I am expecting “-1” as a result. but What I got is “0” because Nov 13 and Oct 31 are within same calendar quarter which is 18-Q4 but they are not in the same Fiscal Quarter.


Does this make sense? I am wondering if it is possible to calculate the number of fiscal quarter between two dates? Thank you.

To add on, why I calculate this “Fiscal FY Quarter Cut Off”? I need this to dynamically display only Current and Previous 4 Fiscal Quarters Information in Bar Chart or case table.

I’ve been working on the quarter scenario, and the logic is a little tougher on this one. (Doing my head in actually)

There will be a solution, I’m just haven’t nailed it down yet. Will be back with something as soon as I can.

Thanks
Sam

This may be too simple, but the closest I’ve got to nailing this after quite some time now is this

Fin. Q Cut Off = 
DIVIDE( Dates[Date] - TODAY(), 90 )

And it actually kind of works out based on my data.

Let me know what you think

This is simple however seems not work for my situation. But, I am able to figure out something that simple & work :slight_smile:

FY Quarter_N-1 = 
CALCULATE( SELECTEDVALUE( Dates[Fiscal Quarter] ),
                    FILTER( ALL( Dates ), Dates[Date] = (TODAY()-90) ) ) 
   FY Quarter_N-2 = 
CALCULATE( SELECTEDVALUE( Dates[Fiscal Quarter] ),
                    FILTER( ALL( Dates ), Dates[Date] = (TODAY()-180) ) )
FY Quarter_N-3 = 
CALCULATE( SELECTEDVALUE( Dates[Fiscal Quarter] ),
                FILTER( ALL( Dates ), Dates[Date] = (TODAY()-270) ) )
FY Quarter_N-4 = 
CALCULATE( SELECTEDVALUE( Dates[Fiscal Quarter] ),
                FILTER( ALL( Dates ), Dates[Date] = (TODAY()-360) ) )
CurrentFYQuarterCutOff = if( Dates[Fiscal Quarter]=Dates[CurrentFY Quarter],999, 
   if(Dates[Fiscal Quarter]=Dates[FY Quarter_N-1],-1,
   if(Dates[Fiscal Quarter]=Dates[FY Quarter_N-2],-2,
        if(Dates[Fiscal Quarter]=Dates[FY Quarter_N-3], -3, 
             if(Dates[Fiscal Quarter]=Dates[FY Quarter_N-4],-4, blank()
)))))

Here is the result:

Thank you Sam

Ok nice one. Good stuff

Sam