Forum Discussion

mtpirhala's avatar
mtpirhala
Regular Visitor
3 years ago
Solved

Calculate Month over Month Labor Cost

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. 

 

DateSales PriceLabor MaterialsTotal Cost (C1+D1)Net
4/1/20211,00010050150850
5/1/20211,000200100300550
6/1/20211,00010025125425
7/1/20211,00015015165260
Total1,000550190740260

 

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 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.