Avg Days Between Orders of Specific Items

[Jlhoffner]

Can anyone help determine number of days between orders (and avg thereof) by groups of customers? The filter contexts would be product #’s or categories (I would put them on a slicer).


The data I have are simple and look like this:

Order #	Cust #	Prod #	Order Date
340623​	8​	130​	10/14/2011 0:00​
292834​	8​	130​	10/9/2008 0:00​
235501​	8​	130​	4/15/2005 0:00​
215475​	9​	100​	12/12/2003 0:00​
215474​	9​	100​	12/12/2003 0:00​
254227​	13​	120​	7/7/2006 0:00​
216356​	13​	120​	1/12/2004 0:00​
204725​	13​	120​	3/14/2003 0:00​

<tbody> </tbody>

I also have a related table of just the unique customer numbers and a calendar table.


You can see that Cust # 8 ordered Product #130 in 2005, 2008, 2011 whereas Cust # 13 ordered item #120 in 2003, 2004, 2006. Smaller intervals. I need to determine the avg number of days between orders of a given individual item by all those customers who have purchased that item (presumably multiple times).

I think this will be somewhat complex inasmuch as several measures will probably need to be built up.

Any help is appreciated.

j

 

[Jacob Barnett]

Hopefully the relatively simple measure below solves your problem - the key is understanding the filter context. The real beauty is that it should be totally portable meaning you can slice it not only by product but by category, customer or product/customer or any other dimensions you have in your 'real' data.

=(MAX(Data[Order Date])-MIN(Data[Order Date]))/COUNTROWS(Data)

Hope this helps (assumes your table is called 'Data').

Btw, congrats on a well written question

 

[Jlhoffner]

Thanks Jacob. I will give this a try. One potential issue I see here is that it won't exclude customers who have only purchased once. That is, never ordered the same item again. It would skew the avg interval down.

 

[Jlhoffner]

Aha! Here's another issue... when you look at the total for an item, it is not aggregating correctly. Here are all the customers who bought a specific item, with avg day between orders for those who ordered more than once ([Repeat Orders] = Countrows(Orders)-1.) The blanks are customers who ordered the item just one time. We can see the grand total isn't aggregating correctly in the context of the use case here. I think the answer lies in use of the ..X functions - probably AVERAGEX.

​	# Repeat Orders	Avg Days Between Orders Simpler
8580	1​	384.0​
21343	4​	158.5​
23861	1​	503.0​
27804	5​	86.0​
30998	​	​
39427	19​	46.4​
40733	​	​
40742	​	​
41085	​	​
41633	​	​
44602	​	​
51351	8​	110.1​
61866	​	​
62067	2​	0.0​
65249	1​	0.0​
82555	3​	0.0​
Grand Total	59​	17.4​

<tbody> </tbody>

 

[Jacob Barnett]

Your nice and straightforward sample data tricked me into thinking this was a more straightforward problem than it really is

Whether mathematically it is exactly right I'm not sure as it depends on how you want to weight the average, but the following measure uses AVERAGEX() to deal with the total issue and iterates it over a table created using SUMMARIZE() which basically groups by Customer and Product number (where AverageDays Raw is the previous measure):

[AverageDays Iterated]=
AVERAGEX (
    SUMMARIZE ( Data, Data[Cust #], Data[Prod #] ),
    [AverageDays Raw]
)

This doesn't deal with your non repeaters. While there might be an elegant way to do this in the measure, I'd be inclined to create a flag in a calculated column to indicate whether that customer/product was a 'repeater' using something like:

=IF (
    CALCULATE (
        COUNTROWS ( Data ),
        FILTER (
             Data,
              Data[Cust #] = EARLIER ( Data[Cust #] ) &&
              Data[Prod #] = EARLIER ( Data[Prod #] )
        )
    )
        > 1,
    "Y",
    "N"
)

You can then reference this in a simple measure that excludes rows from the table that are not repeats:

[Average Days] = CALCULATE([AverageDays Iterated], Data[Repeat]="Y")

Hoping this gets you close, let me know how it goes.