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 Date Account Region Amount 31/01/2016 n123456 US 10 31/03/201...
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

     

     

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