Calculate the number of days between orders then do an average for each items

[Kiwizqt]

Hi folks,

I'm struggling over here to calculate the number of days my orders are spaced inbetween. I would then calculate an average based on a sub-group, but I can't get that first step right. There seems to be lots of solutions with DAX but I'm stuck with PQ. There is only one date per day per item.

Here's a picture, ultimately i'd like to know on average how many days does it take for an order to pop for item 1001: 2days, 1002: 3days etc, I don't know if I should do a rolling difference but if I did it would have to reset on the next item and I don't know how to do that, I would then do an average between the differences to get my result.

https://i.imgur.com/ndFf2qj.png 

I hope that was clear enough, tell me if not i'd be happy to explain myself better.

Cheers to you wizards!

[v-yingjl · Accepted solution]

Hi @Kiwizqt ,

Here it the whole query based on your sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdFLDoAgDATQu7A2aadAkLMY738NqxuJMwsTVi8D/XAcBe4oWwkPt2nYy7l9MarCTog8CqfCCKlcKrU6K0w8AGsKIaJBvWbOMAhVMI2xZqsKZZLrVF7+je+e4kFMa9Z/GtRltN+4C4xB2JfJV8zPPC8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item"}, {{"Data", each _, type table [Item=nullable number, Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Diff", each List.Max([Data][Date])-List.Min([Data][Date])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Diff", Int64.Type}}),
    Custom1 = Table.AddColumn(#"Changed Type1","count", each let Item=[Item] in Table.RowCount(Table.SelectRows([Data],each Item = [Item]))),
    #"Added Custom1" = Table.AddColumn(Custom1, "Average", each [Diff] / [count]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"count", Int64.Type}, {"Average", type number}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Changed Type2", "Data", {"Date"}, {"Data.Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Diff", "count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Data.Date", "Date"}})
in
    #"Renamed Columns"

Attached a sample file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kiwizqt]

Hi @v-yingjl, thanks for taking the time to drop by!

That's a freaking clever solution yet simple ! Thank you very much, that will be of great use to me!