Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
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/
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/
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!
User | Count |
---|---|
102 | |
89 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |