Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Subtract previous days using M-Query

Hi, I have a table that contains (Date, Category and Sales)   Date Category Sales 2024-01-01 A 300 2024-01-01 B 400 2024-01-02 A 1000 2024-01-02 B 500 2024-01-03 A 160...
Need Help
  • Omid_Motamedise's avatar
    Omid_Motamedise
    1 year ago

    Hi Anonymous 
    use this formula

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MAQiJR0lRyA2NjBQitVBk3ACYhN0CSOoDkMDbDIgLaboEsYwLWbYZEBaLEASsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Category = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category", type text}, {"Sales", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Sales]-(List.Sum(Table.SelectRows(#"Changed Type", (x)=>x[Date]<[Date] and x[Category]=[Category])[Sales])??0))
in
    #"Added Custom"
v-saisrao-msft's avatar
v-saisrao-msft
Icon for Community Support rankCommunity Support
1 year ago

Hi Anonymous 

May I ask if you have resolved this issue? If so, please mark their reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.