Forum Discussion

Jackisover's avatar
Jackisover
Icon for Advocate I rankAdvocate I
3 years ago
Solved

Evolution rate only for common months

Hello guys,   I'm trying to display an evolution rate which compares datas over 2 years. BUT, I only want common months to be part of the calculation. (In 2022, I have datas for 6 months, and in 20...
common
DAX
evolution
Help
need
period
rate
  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    Hi Jackisover ,

     

    You can try this method:

    For the 04/01/2022, you can do this:

    MeasureN = CALCULATE(SUM(file[Data N]), FILTER('file','file'[Date] <> DATE(2022,4,1)))
    MeasureN-1 = CALCULATE(SUM(file[Data N-1]), FILTER('file', 'file'[Date N-1] <> MIN('file'[Date N-1])))
    Result = DIVIDE([MeasureN] - [MeasureN-1], [MeasureN-1])

    The result is:

    Hope this helps you.

     

    Best Regards,

    Community Support Team _Yinliw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Jackisover's avatar
Jackisover
Icon for Advocate I rankAdvocate I
3 years ago

Hello v-yinliw-msft and thanks a lot for your work.

 

The rate is correct, but what happenned to my data for 01/04/2022 ? I need it to be seen on my dashboard, and it seems it's not in your file. Check out my screenshot :

 

Also I dont understand the importance of a "date n-1"

 

Thank you for your help

v-yinliw-msft's avatar
v-yinliw-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi Jackisover ,

 

Because I looked your sample data that I think the April seems not one of the five common months?

Could you please explain to me what calculation do you want for the 01/04/2022?

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Jackisover's avatar
    Jackisover
    Icon for Advocate I rankAdvocate I
    3 years ago

    v-yinliw-msft 

     

    Indeed, april is the problematic month because I don't have any data in april 2021. But I still wish to display datas for april 2022. 

    My charts and slicers are OK : if i select 2022 i see from april to september, and if i select 2021, i see from may to september.

    But my rate is wrong. It displays "18%" instead of "8%" because april 2022 is integrated to the calculation. I'd like the rate to calculate only the 5 common months but still keep my datas for other charts .

    • Jackisover's avatar
      Jackisover
      Icon for Advocate I rankAdvocate I
      3 years ago

      🆙

      If someone can help me find a solution...

      Thanks !

      • v-yinliw-msft's avatar
        v-yinliw-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi Jackisover ,

         

        You can try this method:

        For the 04/01/2022, you can do this:

        MeasureN = CALCULATE(SUM(file[Data N]), FILTER('file','file'[Date] <> DATE(2022,4,1)))
        MeasureN-1 = CALCULATE(SUM(file[Data N-1]), FILTER('file', 'file'[Date N-1] <> MIN('file'[Date N-1])))
        Result = DIVIDE([MeasureN] - [MeasureN-1], [MeasureN-1])

        The result is:

        Hope this helps you.

         

        Best Regards,

        Community Support Team _Yinliw

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.