How to calculate Current Fiscal Year Value

Wang123 · October 31, 2018, 3:27pm

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:

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.

sam.mckay · October 31, 2018, 11:31pm

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 )

Wang123 · November 1, 2018, 1:15pm

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.

Wang123 · November 1, 2018, 1:24pm

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.

sam.mckay · November 1, 2018, 9:19pm

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

sam.mckay · November 1, 2018, 9:20pm

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.

Wang123 · November 9, 2018, 1:01am

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.

sam.mckay · November 11, 2018, 10:01pm

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

Wang123 · November 13, 2018, 4:08pm

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.

sam.mckay · November 14, 2018, 7:28am

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

sam.mckay · November 16, 2018, 7:30am

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

Wang123 · November 16, 2018, 9:11pm

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

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

sam.mckay · November 16, 2018, 11:37pm

Ok nice one. Good stuff

Sam