Calculate Month over Month Labor Cost

[mtpirhala]

Good morning, 

Thanks for the information you provide. I am new to Power BI and have gotten so much information from this forum already. 

I have a table that I am trying to adjust and want to use Power Query. In the example table (see below), I have the original sales price for a project and I want to get a running total of the labor/material costs by month. I have seen tuturials on adding running totals month over month, but I would need to subtract month over month. 

Date	Sales Price	Labor	Materials	Total Cost (C1+D1)	Net
4/1/2021	1,000	100	50	150	850
5/1/2021	1,000	200	100	300	550
6/1/2021	1,000	100	25	125	425
7/1/2021	1,000	150	15	165	260
Total	1,000	550	190	740	260

The Sales Price stays the same as it is for one project, while the labor and materials continues to reduce the profit month over month. Similar to a ledger. The Net is essentially just B2-D2 for the first row (Net = 850) then the subsequent rows subtract from each other (E3=E2-D3), so on and so forth. 

Is it possible to add another column and formula in Power Query? 

Thanks so much for any help you can provide. 

Myriah 

[v-yanjiang-msft · Accepted solution]

Hi @mtpirhala ,

According to your description, here's my solution.

Add a custom column.

[Sales Price]-List.Sum(Table.SelectRows(#"Changed Type",(x)=>x[Date]<=[Date])[Total Cost])

Get the correct result.

Here's the whole M syntax, you can copy-paste in a blank query to see the details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE31DcyMDJU0lEy1DEwMADRYNIUzASSsTrRSqaYqoyQ1BobQJSZ4TLMyBTEBJIgVeZYVEFtAxFmQFWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Sales Price" = _t, #"Labor " = _t, Materials = _t, #"Total Cost" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales Price", Int64.Type}, {"Labor ", Int64.Type}, {"Materials", Int64.Type}, {"Total Cost", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Net", each [Sales Price]-List.Sum(Table.SelectRows(#"Changed Type",(x)=>x[Date]<=[Date])[Total Cost]))
in
    #"Added Custom"

I also attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.