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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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

 

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

View solution in original post

2 REPLIES 2
Pragati11
Super User
Super User

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

 

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors