Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
ahood89
Regular Visitor

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+P2. I now want to un-do that and just have P2 so P2-P1. I have looked at various online help articles but I am getting nowhere! I think I am confusing Measues and Columns as well which isn't helping and I don't understand the limitations of either/how they work. 

 

I can do it in Excel so easily so I feel like this should be simple in PBI.

 

Excel:

ahood89_0-1659962574492.png

In PBI:

ahood89_1-1659962691345.png

Column = IF('TAGETIK EXTRACTS'[Period] = 2,CALCULATE(SUMX('TAGETIK EXTRACTS','TAGETIK EXTRACTS'[Transaction Amount €]),('TAGETIK EXTRACTS'[Period]=2))-CALCULATE(SUMX('TAGETIK EXTRACTS','TAGETIK EXTRACTS'[Transaction Amount €]),('TAGETIK EXTRACTS'[Period]=1)),0)
 
If that worked I was then going to expand it e.g.
Column = IF('TAGETIK EXTRACTS'[Period] = 2,CALCULATE(SUMX('TAGETIK EXTRACTS','TAGETIK EXTRACTS'[Transaction Amount €]),('TAGETIK EXTRACTS'[Period]=2))-CALCULATE(SUMX('TAGETIK EXTRACTS','TAGETIK EXTRACTS'[Transaction Amount €]),('TAGETIK EXTRACTS'[Period]=1))
IF('TAGETIK EXTRACTS'[Period] = 3,CALCULATE(SUMX('TAGETIK EXTRACTS','TAGETIK EXTRACTS'[Transaction Amount €]),('TAGETIK EXTRACTS'[Period]=3))-CALCULATE(SUMX('TAGETIK EXTRACTS','TAGETIK EXTRACTS'[Transaction Amount €]),('TAGETIK EXTRACTS'[Period]=2)),0)
 
Any ideas please?
 
Thanks,
5 REPLIES 5
ahood89
Regular Visitor

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):

FilePeriodDepartmentTypeAmount
Budget1ARevenue1
Budget1BCost2
Budget2ARevenue3
Budget2BCost4
Budget3ARevenue6
Budget3BCost7
Budget4ARevenue10
Budget4BCost11
Actual1ARevenue1
Actual1BCost2
Actual2ARevenue3
Actual2BCost4
Actual3ARevenue6
Actual3BCost7
Actual4ARevenue10
Actual4BCost11

Desired result (example of 1 sub group):

FilePeriodDepartmentTypeAmountAmount per Period
Budget1BCost22
Budget2BCost42
Budget3BCost73
Budget4BCost114

 

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

 

AntonioM_0-1659969521204.png

 

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

ahood89_0-1659992660671.png

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. 

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.

AntonioM
Solution Sage
Solution Sage

You could add a custom column

 

Periodic Revenue = 
'Table'[Measure] - MAXX(FILTER('Table', 'Table'[Period] < EARLIER('Table'[Period])), 'Table'[Measure])

 

Where Table is your table name and Period and Measure are the columns from your screenshot

 

This will work if you only have one year's data (so one set of periods 1 through 12)

 

 

made up example:

AntonioM_0-1659964319084.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.