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
fjdarnell
New Member

running total with IDs and nonsequential dates

Hi. I am struggling to figure out how to create a running total of points by ID and by nonsequential date.  This would include multiple months for a given ID to show progress over time. Any help is truly appreciated. 

 

Snipaste_2022-05-17_14-40-20.png

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc3LDYAwDAPQXXKulNqhpcxSdf81AKmfgOCY58iuVUCTIIhKKiNxHZQWZkDFcHPema93U/tm/vAxvJenvN+rqz1535SzvzwDFLfQTg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Points = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Points", Int64.Type}}),
    ListOfIDs = List.Buffer(#"Changed Type"[ID]),
    ListOfDates = List.Buffer(#"Changed Type"[Date]),
    ListOfPoints = List.Buffer(#"Changed Type"[Points]),
    //Function Start
    fxGetCumTotal=(ListOfIDs, ListOfPoints)=>
        let
            FunctionResult = List.Generate(()=>[x=ListOfPoints{0},i=0], each [i]<List.Count(ListOfPoints), each [i=[i]+1, x=(if ListOfIDs{i}=ListOfIDs{[i]} then [x] else 0) + ListOfPoints{i}], each [x])
        in
            FunctionResult,
    //Function End
    Result = Table.FromColumns(Table.ToColumns(#"Changed Type")&{fxGetCumTotal(ListOfIDs, ListOfPoints)},Table.ColumnNames(#"Changed Type")&{"Running Total"})
in
    Result

View solution in original post

2 REPLIES 2
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc3LDYAwDAPQXXKulNqhpcxSdf81AKmfgOCY58iuVUCTIIhKKiNxHZQWZkDFcHPema93U/tm/vAxvJenvN+rqz1535SzvzwDFLfQTg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Points = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Points", Int64.Type}}),
    ListOfIDs = List.Buffer(#"Changed Type"[ID]),
    ListOfDates = List.Buffer(#"Changed Type"[Date]),
    ListOfPoints = List.Buffer(#"Changed Type"[Points]),
    //Function Start
    fxGetCumTotal=(ListOfIDs, ListOfPoints)=>
        let
            FunctionResult = List.Generate(()=>[x=ListOfPoints{0},i=0], each [i]<List.Count(ListOfPoints), each [i=[i]+1, x=(if ListOfIDs{i}=ListOfIDs{[i]} then [x] else 0) + ListOfPoints{i}], each [x])
        in
            FunctionResult,
    //Function End
    Result = Table.FromColumns(Table.ToColumns(#"Changed Type")&{fxGetCumTotal(ListOfIDs, ListOfPoints)},Table.ColumnNames(#"Changed Type")&{"Running Total"})
in
    Result

Vijay, thank you. This is what I was looking for. You're awesome!

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