PowerQuery Cumulative/Running Total with less than condition on Date

[EiliseH]

Hello,

I'm hoping someone here can help.

I have a data set which records how much is spent per week on each project by each role in each stage of work.

In PowerQuery (not DAX), I want to add a cumulative expenditure column for each project-role-stage.

Form searching, I think the first step is to to group by project, role and stage, but I'm not sure where to go from there?

I have pasted some sample data below, with my desired result in the ExpenditureToDate column.

In Excel I have used the below formula to acheive my desired results:

=SUMIFS([ExpenditureInWeek],[Project],[@Project],[Role],[@Role],[Stage],[@Stage],[WeekEnding],"<="&[@WeekEnding])

Project	Role	Stage	WeekEnding	ExpenditureInWeek	CumulativeExpenditure
A	Façade Specialist	Stage 4	07/07/2024	3000	3000
A	Façade Specialist	Stage 4	14/07/2024	3000	6000
A	Façade Specialist	Stage 4	21/07/2024	3000	9000
A	Façade Specialist	Stage 4	28/07/2024	3000	12000
A	Façade Specialist	Stage 5	28/07/2024	2000	2000
A	Façade Specialist	Stage 5	04/08/2024	3000	5000
A	Façade Specialist	Stage 5	11/08/2024	1000	6000
A	Architect	Stage 4	14/07/2024	200	200
A	Architect	Stage 4	21/07/2024	100	300
B	Façade Specialist	Stage 4	18/08/2024	5000	5000
B	Façade Specialist	Stage 4	25/08/2024	1000	6000
B	Façade Specialist	Stage 4	01/09/2024	600	6600
B	Façade Specialist	Stage 4	08/09/2024	400	7000

[EiliseH]

Thank you so much, @lbendlin, this works great!

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlHSUQrKz0kFUsEliekgOjw1Nds1LyUzLx3Ica0oSAWyS0qLUj3zQDJKsTrRSo5AGbfEw8sTU1IVggtSkzMTczKLS2BmKJgAWQbm+kBkZGAE4hgbGBgQq9HQhEyNRobkarQgQ6MpukYjEjQaAP1oQY6NhoZIGg2RNToWJWdklkAiFEdgGhFUjhKEhlDlTkTEmQWSs0wNiNdoZIrNP0RoNAC61BKm0YwEfRZI+kxA+mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ExpenditureInWeek", Currency.Type}, {"WeekEnding", type date}},"en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CumulativeExpenditure", (k)=> List.Sum(Table.SelectRows(#"Changed Type", each [Project]=k[Project] and [Role]=k[Role] and [Stage]=k[Stage] and [WeekEnding]<=k[WeekEnding])[ExpenditureInWeek]),Currency.Type)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.