cancel
Showing results for
Did you mean:
Regular Visitor

## Remove rolling total/cumulative effect from a sequence of amounts

Hey there,

I want to remove rolling total/cumulative effect from a sequence of amounts?

My data looks like this

 Date Account Region Amount 31/01/2016 n123456 US 10 31/03/2016 n123456 US 25 31/08/2016 n123456 US 70 30/09/2016 n123456 US 71 29/02/2016 x987654 Canada 50 31/03/2016 x987654 Canada 305 30/04/2016 x987654 Canada 1000 30/09/2016 x987654 Canada 5000

What I would like to calculate is this:

 Date Account Region Amount 31/01/2016 n123456 US 10 31/03/2016 n123456 US 15 31/08/2016 n123456 US 45 30/09/2016 n123456 US 1 29/02/2016 x987654 Canada 50 31/03/2016 x987654 Canada 255 30/04/2016 x987654 Canada 695 30/09/2016 x987654 Canada 4000

Is there a nice way for me to achieve this using DAX/Power Query ?

Thanks

@amitchandak  @Pragati11

1 ACCEPTED SOLUTION
Super User III

First thing whihc is very important, do add the sample data in such a format that anyone can copy and paste it in Power BI easily. Best way of doing it is sharing it in a csv file and uploading it to dropbox and then sharing the dropbox link here.

Also, I am not sure your output sample is right. The highlighted row should return 21 not 50. Because the calculation is previous row amount subtracted from current row AMOUNT. Please confirm this.

If my understanding is right on your question, You can try something as below.

Fisrt I created an INDEX column on your sample data:

Then I created the following DAX caluclation:

CalcAmount =
VAR currentIndex =
MAX ( testTable[Index] )
VAR currentAmt =
SUM ( testTable[Amount] )
RETURN
ABS (
currentAmt
- (
CALCULATE (
SUM ( testTable[Amount] ),
FILTER ( ALL ( testTable ), testTable[Index] = currentIndex - 1 )
)
)
)

The output is shown as below:

The output displays an ABSOLUTE value.

Thanks,

Pragati

My Blog: Data Vibe

If this helps, Appreciate a KUDOS!

Proud to be a Super User!

2 REPLIES 2
Super User III

First thing whihc is very important, do add the sample data in such a format that anyone can copy and paste it in Power BI easily. Best way of doing it is sharing it in a csv file and uploading it to dropbox and then sharing the dropbox link here.

Also, I am not sure your output sample is right. The highlighted row should return 21 not 50. Because the calculation is previous row amount subtracted from current row AMOUNT. Please confirm this.

If my understanding is right on your question, You can try something as below.

Fisrt I created an INDEX column on your sample data:

Then I created the following DAX caluclation:

CalcAmount =
VAR currentIndex =
MAX ( testTable[Index] )
VAR currentAmt =
SUM ( testTable[Amount] )
RETURN
ABS (
currentAmt
- (
CALCULATE (
SUM ( testTable[Amount] ),
FILTER ( ALL ( testTable ), testTable[Index] = currentIndex - 1 )
)
)
)

The output is shown as below:

The output displays an ABSOLUTE value.

Thanks,

Pragati

My Blog: Data Vibe

If this helps, Appreciate a KUDOS!

Proud to be a Super User!

Solution Sage

You can try this in blank query.

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc69DoAgDATgd2E26bVQkNlHME7GgcSZ2ccXfydgaW740rt1NZYJTAL2ZjCZxTq90jKXwzDb8BDbIKI/GRskvF9AiC3CN5FIkI8ccQxeXUlTymlPJWhlT4VZ6N/oOo6ByrRq7wsRiHsDww23Ew==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Account = _t, Region = _t, Amount = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"Amount", type number }}),
GR = Table.RemoveColumns( Table.AddIndexColumn(Table.Group(ChangeType, {"Account","Region"}, {{"Gr", each _, type table }}),"Index"),{"Account", "Region"}),
#"Expanded Gr" = Table.ExpandTableColumn(GR, "Gr", {"Date", "Account", "Region", "Amount"}),
Index = List.Buffer(#"Expanded Gr"[Index]),
Amounts = List.Buffer(ChangeType[Amount]),
ReversedRT = List.Generate( () => [RRT = Amounts{0}, i=0],
each [i] < List.Count(Amounts),
each try if Index{[i]}= Index{[i]+1}
then [RRT = Amounts{[i]+1} - Amounts{[i]}, i = [i]+1]
else [RRT = Amounts{[i]+1}, i = [i]+1]
otherwise [i = [i]+1],
each [RRT]),
FINAL = Table.FromColumns({ChangeType[Date],ChangeType[Account],ChangeType[Region],ChangeType[Amount],ReversedRT}, {"Date","Account","Region","Amount","RRT"})
in
FINAL``````

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors