Forum Discussion

Jackisover's avatar
Jackisover
Advocate 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 20221 I have datas for 5 months). I just want my measure to calculate the rate between the 5 common months. 

 

Here is an extract of my data.

 

And here is my DAX measure : 

Rate N /N-1 = 
divide ( sum('file'[Data N])-sum('file'[Data N-1]),sum('file'[Data N-1]),"NA")

 

Of course, I want to be able do display my charts for each year, using a slicer for years. 

 

Can someone give me the solution please ? I'm sure it's not that difficult !

 

Thans a lot !

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.

     

7 Replies

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

    Hi Jackisover ,

     

    I use your data and DAX to have a try.

    And then I create a column:

    Date N-1 = 'file'[Date] - 365

    Then I make a chart like this:

     

     

    Hope this helps you. Here is my PBIX file.

     

     

     

    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
      Advocate 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
        Community 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.