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"