Cumulative Total

[KL718]

I want to calculate cumulative total. I have gone through other post but my dataset do not have Dates column.

[hnguy71]

You can achieve this via Power Query:

The meat of the code works starting from GroupMe and all the way down:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyNAASSrE6QK4RkGWE4BoDWcYQbiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Part = _t, Sales = _t, dd = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", type text}, {"Sales", Int64.Type}}),
    GroupMe = Table.AddColumn(#"Changed Type", "GroupMe", each 1),
    #"Added Index" = Table.AddIndexColumn(GroupMe, "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"GroupMe"}, {{"All_Rows", each _, type table}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "All_Rows", "All_Rows - Copy"),
    #"Expanded All_Rows" = Table.ExpandTableColumn(#"Duplicated Column", "All_Rows", {"Part", "Sales", "Index"}, {"Part", "Sales", "Index"}),
    #"Expanded All_Rows - Copy" = Table.ExpandTableColumn(#"Expanded All_Rows", "All_Rows - Copy", {"Part", "Sales", "Index"}, {"Part.1", "Sales.1", "Index.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All_Rows - Copy", "Find_Cumulative", each [Index] >= [Index.1]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Find_Cumulative] = true)),
    #"Regroup for Cumulative" = Table.Group(#"Filtered Rows", {"GroupMe", "Sales", "Index", "Part"}, {{"Cumulative Total", each List.Sum([Sales.1]), type number}})
in
    #"Regroup for Cumulative"

[KL718]

Thanks.

Can do same with DAX?

[Anonymous · Accepted solution]

Hi @KL718 

1. Create and Index column using Edit query starting from 1.

2.  Go through the link

       https://javierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/

3. Use the Index as the column for Earlier function.

Cheers

CheenuSing