Calculate average interval between customer orders

[cr731]

I am using Power Query and have a table containing customers along with corresponding order dates. I would like to calculate the average interval (in months) between orders for each given customer. For instance, is the average time span 1, 2, 3 months etc.

The dates will only represent the fiscal period so will be things like 5/1/2016, 7/1/2016, 10/1/2016 and so forth.

What would be the best way to do that?

This will be used on a table with about 1 million rows so as efficient a solution as possible is preferred.

Thanks

 

[Comfy]

Hi,

Source Table:

Excel 2010
	A	B	C
1	Customer	Order Date
2	A	01/01/2001
3	B	01/01/2001
4	C	02/01/2001
5	A	02/01/2001
6	B	01/02/2001
7	A	01/03/2001
8	B	01/03/2001
9	C	01/04/2001
10	A	01/04/2001
11	B	01/04/2001
12
Sheet1

Results:

Excel 2010
	E	F	G
1	Customer	Order Date	AvgDuration
2	A	[Table]	30
3	B	[Table]	30
4	C	[Table]	89
5
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"Order Date", each _, type table}}),
    AvgFunc = (CustTable as table) as duration =>
        let
            SortDate = Table.Sort(CustTable, {{"Order Date", Order.Ascending}}),
            AddIndex = Table.AddIndexColumn(SortDate, "Index", 0, 1),
            DifCol = Table.AddColumn(AddIndex, "Duration", each try AddIndex[Order Date]{[Index]}-AddIndex[Order Date]{[Index]-1} otherwise 0),
            RemoveZero = Table.SelectRows(DifCol, each ([Duration] <> 0)),
            TypeDuration = Table.TransformColumnTypes(RemoveZero,{{"Duration", type duration}}),
            CalcAvg = List.Average(TypeDuration[Duration])
        in
            CalcAvg,
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "AvgDuration", each AvgFunc([Order Date])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"AvgDuration", type duration}}),
    #"Extracted Days" = Table.TransformColumns(#"Changed Type1",{{"AvgDuration", Duration.Days, Int64.Type}})
in
    #"Extracted Days"

I've left all the columns in so you can delete and format as required.

Also the result is in days so you can divide by 30/31 etc.... but I've left that up to you.