Forum Discussion

ahood89's avatar
ahood89
Regular Visitor
4 years ago

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+...
Need Help
ahood89's avatar
ahood89
Regular Visitor
4 years ago

Thanks. I can also get this to work for my test data:

But unfortunately it isn't working for my actual dataset. When adding the new column and Period to a table, it still shows the accumulated values i.e. the same as the SUM of (my equivalent of) Test[Amount]? There are many more columns and rows of data in the real data as opposed to my example, but unfortunately I can't share it. 

 

My real data contains negative values, but I just did the above test data with negative values and it was OK so I don't think it can be that. 

AntonioM's avatar
AntonioM
Solution Sage
4 years ago

Ok, so for more columns can you give this a try?

 

Amount per Period = 
var _period = 'Table'[Period]

var _previous = 
CALCULATE(
    MAX('Table'[Amount]),
    'Table'[Period] < _period,
    ALLEXCEPT('Table','Table'[File],'Table'[Department],'Table'[Type])
    )

return 'Table'[Amount] - _previous

 

The columns inside the ALLEXCEPT need to be the ones you're looking to group by.