How do I subtract a value based upon the difference in date?

Hi Anonymous ,

You can sort your table on [FruitName] and [Date], then merge the table on itself using two indexes.

A full explanation and example query here:

https://community.powerbi.com/t5/Power-Query/Group-By-with-Index-and-Calculated-Column/m-p/3042053#M96560 

Pete

Hi Anonymous 

I create a sample by following Pete's solution. Hope this helps. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KzEtPLVbSUTI00gdCAyMjINvC0lgpVidaKSAzLzWxoCAHQ4GpuRlYAZJ2Q30TuKyFARbtSAosTUwxtZsitGOzHUmBOdR8FO1ABJO2NMKqH6HCAmRALAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Date = _t, #"Count total per day" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"Date", type date}, {"Count total per day", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Fruit", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Fruit", "Index"}, #"Added Index1", {"Fruit", "Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Count total per day"}, {"Count total per day.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"Count total per day.1", "Previous Count total per day"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Difference", each [Count total per day] - [Previous Count total per day]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Previous Count total per day"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.