Forum Discussion
Un-accumulate a set values
I have a set of finance data that I have already had to transform and combine in Power Query, including applying exchange rates. The values are accumulated with each Period. So P1 = P1, P2 = P1+...
Thanks for replying so quickly.
Apologies, I have multiple sets of periods. e.g. 2021 Actual, 2021 Budget, 2022 Actual, 2022 Budget etc. Even though on the current page I have a Filter so there is only 1 of each period.
Also, sorry I should have said before, the screenshots are not the raw data, they're already 'grouped'. The raw data is more like the below, so multiple entries for 1, 2 etc.
I did as you suggested above and only P1 is correct, I am assuming that's something to do with this?
Raw data structure example (simplified):
| File | Period | Department | Type | Amount |
| Budget | 1 | A | Revenue | 1 |
| Budget | 1 | B | Cost | 2 |
| Budget | 2 | A | Revenue | 3 |
| Budget | 2 | B | Cost | 4 |
| Budget | 3 | A | Revenue | 6 |
| Budget | 3 | B | Cost | 7 |
| Budget | 4 | A | Revenue | 10 |
| Budget | 4 | B | Cost | 11 |
| Actual | 1 | A | Revenue | 1 |
| Actual | 1 | B | Cost | 2 |
| Actual | 2 | A | Revenue | 3 |
| Actual | 2 | B | Cost | 4 |
| Actual | 3 | A | Revenue | 6 |
| Actual | 3 | B | Cost | 7 |
| Actual | 4 | A | Revenue | 10 |
| Actual | 4 | B | Cost | 11 |
Desired result (example of 1 sub group):
| File | Period | Department | Type | Amount | Amount per Period |
| Budget | 1 | B | Cost | 2 | 2 |
| Budget | 2 | B | Cost | 4 | 2 |
| Budget | 3 | B | Cost | 7 | 3 |
| Budget | 4 | B | Cost | 11 | 4 |
So Amount per Period is the value I am trying to create.
Sure, this works for the data you posted, is the real data much more complicated?
Amount per Period =
var _period = 'Table'[Period]
var _previous =
CALCULATE(
MAX('Table'[Amount]),
'Table'[Period] < _period,
ALL('Table'[Amount])
)
return 'Table'[Amount] - _previous