Forum Discussion

Heybee's avatar
Heybee
Frequent Visitor
7 years ago
Solved

Variation of count - between Fiscal Years

Hello Everyone, I am struggling to calculate a variation of count between fiscal years.

 

1- I created my fiscal year column using this formula.

 

_FiscalYearNumber_ = if(MONTH('MyTable'[date_column]) <= 9, VALUE(FORMAT('MyTable'[date_column],"YYYY")),VALUE(FORMAT('MyTable'[date_column],"YYYY"))+1)

 

 

_FiscalYearText_ = "FY "&Right(Format('MyTable'[_FiscalYearNumber_],"0#")-1,2)&"-"&Right(FORMAT('MyTable'[_FiscalYearNumber_],"0#"),2)

I used the FiscalYearText in the Axis of my clustered column chart

 

2-  Using  the _FiscalYearNumber_, I can calculate the count for the Actual Fiscal Year... like this (a new measure):

 

_card_count_A-FY_ = CALCULATE(count('MyTable'[card]),'MyTable'[_FiscalYearNumber_])

Now this is where I am stucked...

 

How to calculate the Last Fiscal Year: _card_count_L-FY_, so I can make a difference be the two (_card_count_A-FY_ and _card_count_L-FY_) and DIVIDE([_card_count_VAR_FY],[_card_count_L-FY_]) to have the VAR %...

 

Thank you for your help and insights!

 

I used this formula... but it does not work

_card_count_L-FY_ = CALCULATE([_card_count_A-FY_],'Mytable'[_FiscalYearNumber_]-1)

 

  • Hi,

     

    I still do not know what you want but i have attempted something.  Download my PBI file from here.

     

    Hope this helps.

     

5 Replies

  • Hi,

     

    It will help if you share the link from where i can download your PBI file and also show the expected result there.

    • Heybee's avatar
      Heybee
      Frequent Visitor

      Ashish_Mathur wrote:

      Hi,

       

      It will help if you share the link from where i can download your PBI file and also show the expected result there.


       

      Hi!

      Please, find the file here: PBI file

       

      The expected result is the variation graph between Fiscal Years.

       

      Hope it is clear.. thank you again!

       

      Regards,

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User

        Hi,

         

        I still do not know what you want but i have attempted something.  Download my PBI file from here.

         

        Hope this helps.