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

[Nida]

Hi All,

I hope you're well,

I wondering if someone might be able to advise me regarding how I can subtract these pseudonymised(fruit values) per day by the previous day to see the increase on a whole or average level. 

This is my table with the columns

This is my current Query I would like to create a new query one which subtract for example apples for 05.11.2022 - 04.11.2022 so I would like to calculate the difference of increase of decrease in number of fruits per day. 

Many thanks

[BA_Pete · Accepted solution]

Hi @Nida ,

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#M... 

Pete

[wdx223_Daniel]

NewStep=let a=Table.Sort(#"Filtered Rows1","Dates") in Table.FromRecords(List.Accumulate(Table.ToRecords(a),{{},[]},(x,y)=>let b=Record.FieldOrDefault(x{1},y[name],0) in {x{0}&{y&[Count=y[#"Count Total per day"]-b]},x{1}&Record.AddField([],y[name],y[#"Count Total per day"])}){0})

[v-jingzhang · Accepted solution]

Hi @Nida 

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.

[BA_Pete · Accepted solution]

Hi @Nida ,

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#M... 

Pete

[Nida]

Hi @BA_Pete,

I apologise for my very late reply. I really appreciate the answer you have posted. However, I am looking to run queries without applying surface level filters and trying to apply simpler queries as shown in my screenshot

Many thanks,

Nida