Forum Discussion
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
- vivran22Community 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/- AnonymousNot 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!