cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Adam_cordell_ma
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

DateAccountRegionAmount
31/01/2016n123456US10
31/03/2016n123456US25
31/08/2016n123456US70
30/09/2016n123456US71
29/02/2016x987654

Canada

50
31/03/2016x987654Canada305
30/04/2016x987654Canada1000
30/09/2016x987654Canada5000


What I would like to calculate is this:


DateAccountRegionAmount
31/01/2016n123456US10
31/03/2016n123456US15
31/08/2016n123456US45
30/09/2016n123456US1
29/02/2016x987654

Canada

50
31/03/2016x987654Canada255
30/04/2016x987654Canada695
30/09/2016x987654Canada4000


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

Thanks

@amitchandak  @Pragati11 

1 ACCEPTED SOLUTION
Pragati11
Super User III
Super User III

Hi @Adam_cordell_ma ,

 

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.

Pragati11_0-1621332525293.png

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

Fisrt I created an INDEX column on your sample data:

Pragati11_1-1621332613523.png

 

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:

Pragati11_2-1621332703987.png

 

The output displays an ABSOLUTE value.

 

Thanks,

Pragati

 

 


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




View solution in original post

2 REPLIES 2
Pragati11
Super User III
Super User III

Hi @Adam_cordell_ma ,

 

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.

Pragati11_0-1621332525293.png

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

Fisrt I created an INDEX column on your sample data:

Pragati11_1-1621332613523.png

 

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:

Pragati11_2-1621332703987.png

 

The output displays an ABSOLUTE value.

 

Thanks,

Pragati

 

 


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




View solution in original post

Jakinta
Solution Sage
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

 

Jakinta_0-1621264379757.png

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors