Forum Discussion

Adam_cordell_ma's avatar
Adam_cordell_ma
Regular Visitor
5 years ago
Solved

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 

DAX
Power Query
  • Pragati11's avatar
    Pragati11
    5 years ago

    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.

    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

     

     

2 Replies

  • Jakinta's avatar
    Jakinta
    Solution Sage
    5 years ago

    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

     

  • Pragati11's avatar
    Pragati11
    Super User
    5 years ago

    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.

    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