Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculation previous month by category

Hello, everyone,

 

I need to calculate, on a table that has dates and values, and different categories for this values, where there is only one value for each permutation of dates and categroies, the value of of the previous month for the same categories as the month in question.

 

I have tried the following, which works for the month in question, but if you try to calculate the previous month, it does not work.

Increment / NCS = 
            CALCULATE(
                SUM('Table'[Value]), 
                PARALLELPERIOD('Table'[MonthYear], -0, MONTH)
            )

 This would be the one that does not work:

Increment / NCS = 
            CALCULATE(
                SUM('Table'[Value]), 
                PARALLELPERIOD('Table'[MonthYear], -1, MONTH)
            )

 

So I assumed I could try to filter by categories before applying the calculation, but I don't seem to be doing it correctly. This is what I have tried:

Increment / NCS = 
            CALCULATE(
                SUM('Table'[Value]), 
                PARALLELPERIOD('Table'[MonthYear], -0, MONTH),
                'Table'[Category1] = 'Table'[Category1],
                'Table'[Category2] = 'Table'[Category2],
                'Table'[Category3] = 'Table'[Category3]
            )

 

Thank you very much for your help!

  • Hello Anonymous ,

     

    You may want to use a different time intelligence function for this. I have created it on a sample data for your reference:

     

     

    Previous Month = CALCULATE([Value Sum],PREVIOUSMONTH(Sheet1[Month]))
    
    where
    Value Sum = SUM(Sheet1[Value])

     

    Regards,
    Vivek

    If it helps, please mark it as a solution

    Kudos would be a cherry on the top 🙂

    https://www.vivran.in/

2 Replies

  • vivran22's avatar
    vivran22
    Community Champion

    Hello Anonymous ,

     

    You may want to use a different time intelligence function for this. I have created it on a sample data for your reference:

     

     

    Previous Month = CALCULATE([Value Sum],PREVIOUSMONTH(Sheet1[Month]))
    
    where
    Value Sum = SUM(Sheet1[Value])

     

    Regards,
    Vivek

    If it helps, please mark it as a solution

    Kudos would be a cherry on the top 🙂

    https://www.vivran.in/

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hello , vivran22 , thanks for your answer, it works like a charm.

       

      Can I ask you why does it not work if you don not calculate the sum() in a different measurement?

       

      Thanks!