Average re-order date per customer

[gazmoz17]

Hi,

Is this possible in excel power query or do I need to learn Power BI?

I have an invoice report I want to use this to work out the average order frequency customer) e.g. 27 days.

Then based on diff between their last order date and todays date.....are they over due to order. Filter the query to show who overdue to order and th expected date they should have ordered.

Any help much appreciated.

 

[gazmoz17]

Dummy data.xlsx

 

[JEC]

For non of the customers I can find the last order date (shown in your picture) in the dataset

 

[gazmoz17]

Hi JEC,

Sorry I used an addin to randomise my data (confidential) in the data table in the first sheet. But the structure is the same as my actual data.

The picture I orig screen grabbed is just my ideal output or something similar. So it prob doesnt directly link to that customers last order date in the data table in tab 1. I just made up some “last
Order dates” in that picture.

Was just wondering if this type of output was possible in excel power query or can this type of thing only be done in power BI?

Thanks

 

[JEC]

Yes it is possible in power query ofcourse

 

[gazmoz17]

Ok thanks. So I know how to get the days difference between 2 date fields in 2 different columns in power query. But I don’t know how to get days between dates in the same column and per customer. And Im pretty sure Ive returned latest date in power query before but I dont know how to put all of it together, im still pretty new to power query. Think I could get earliest and latest date of order per customer but I dont know to get diff between each sequential order for same customer to then take an average from.

 

[JGordon11]

with an excel data table named Table1, having a column DATE and a column COMPANY (other columns names don't matter), try:

let
    Source = Table.Buffer(Table.TransformColumnTypes(Table.SelectColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], {"DATE", "COMPANY"}),{{"DATE", type date}})),
    Avg = "Average days between orders",
    fxAvg = each Record.Field(_, Avg),
    today = Date.From(DateTime.LocalNow()),
    tbl1 = Table.Group(Source, "COMPANY", {Avg, each let 
                min = List.Min([DATE]),max = List.Max([DATE]), n = Table.RowCount(_)
            in {Number.RoundUp(Duration.Days(max-min)/(n-1)), max}}),
    lst =   {
                {"Last Order", each fxAvg(_){1}},
                {"Expected Order Date", each try Date.AddDays([Last Order], fxAvg(_){0} ) otherwise Date.From(99999)},
                {"Deficit", each Duration.Days([Expected Order Date] - today)}
            },
    tbl2 = List.Accumulate(lst, tbl1, (s,c)=> Table.AddColumn(s,c{0},c{1})),
    tbl3 = Table.SelectRows(tbl2, each [Deficit] < 0),
    tbl4 = Table.TransformColumns(tbl3, {Avg, each _{0}}),
    tbl5 = Table.Sort(tbl4,"Deficit"),
    Result = Table.RenameColumns(tbl5, {"Deficit", "Deficit as of " & Text.From(today)})
in
    Result

 

[gazmoz17]

Hi JGordon 11, only been able to look at this now as didnt take my laptop home with me over the weekend.

Absoloutely brilliant thank you . Dont want to be lazy so Im dissecting the steps so I can maybe use the logic down the line for otehr things. Really appreciate your time there.