Average Days between Orders

[cplesner]

Hi

I'm trying to make a measure that shows the average days between orders from a specific Customer plus another measure that shows the average days between the orders of a specific Product from each Customer.

Below is the example where i have typed in the days manually, which is basically just a datediff.

Ex. Cust A order Product A for the first time on 01-01-2016. 60 Days later the same customer orders again, while the same Customer orders the same product 83 later.

Eventuelly the output should show that for example

Cust A is ordering Product A on average every 83 Days (2 Orders)

Cust A is ordering Product B on average every 32,5 Days (3 Order)

Cust A is ordering on average 31,25 Days (5 Order)

I can get these to the number by calculating the differences in SQL, but i would like to keep it in DAX

Thanks in advance

[v-qiuyu-msft]

Hi @cplesner,

In your scenario, you can create two measures like below:

DateDiff-ProductCustomer = VAR currentday = MIN( Table1[Date] )
RETURN
( IF(
	ISERROR( 
DATEDIFF(  currentday,
CALCULATE( MAX( Table1[Date] ), FILTER( ALLEXCEPT(Table1, Table1[Product], Table1[Customer]), Table1[Date] < MAX( Table1[Date] ) )),DAY )),

DATEDIFF(  CALCULATE( MAX( Table1[Date] ), FILTER( ALLEXCEPT(Table1, Table1[Product], Table1[Customer]), Table1[Date] < MAX( Table1[Date] ) )),currentday,DAY) )
)

DateDiff-Customer = 
VAR currentday = MIN( Table1[Date] )
RETURN
( IF(
	ISERROR( 
DATEDIFF(  currentday,
CALCULATE( MAX( Table1[Date] ), FILTER( ALLEXCEPT(Table1, Table1[Customer]), Table1[Date] < MAX( Table1[Date] ) )),DAY )),

DATEDIFF(  CALCULATE( MAX( Table1[Date] ), FILTER( ALLEXCEPT(Table1, Table1[Customer]), Table1[Date] < MAX( Table1[Date] ) )),currentday,DAY) )
)

Best Regards,
Qiuyun Yu

[cplesner]

Hi Qiuyun

Thanks for the answer, it works fine if you actively use the date in the pivot. However I would prefer to have it as a calculated column/calculated table so that i can do an average measure on it and i don't have to actively use the Date attribute.

I have solved it in SQL creating another table like below using Partition by and Rank, but DAX would be preffered

WITH Ranked as (

SELECT a.[Product],a.[Customer],a.[Date],

rank() over (PARTITION BY a.[Product],a.[Customer] order by a.[Date]) as rankid,

a.Amount

FROM [Table1] a

)

select

r1.[Product],

r1.[Customer],

r1.[Date],

r1.rankid,

datediff(d,r2.[Date],r1.[Date]) as DaysBetween,

1 as InvCounter

FROM Ranked r1 LEFT OUTER JOIN Ranked r2 ON

r1.[Product]=r2.[Product] and r1.[Customer]=r2.[Customer] and

(r1.rankid=r2.rankid+1)

[v-qiuyu-msft · Accepted solution]

Hi @cplesner,

You an also create calculated columns to calculate DateDiff values based for specific customer, or both customer and product. See:

Previous-Customer = CALCULATE(MAX(Table1[Date]), (FILTER(Table1, EARLIER(Table1[Customer])=Table1[Customer] && EARLIER(Table1[Date])>Table1[Date])))

DateDiff-C = DATEDIFF('Table1'[Previous-Customer],'Table1'[Date],DAY)

Previous-PC = CALCULATE(MAX(Table1[Date]), (FILTER(Table1, EARLIER(Table1[Customer])=Table1[Customer] && EARLIER(Table1[Product])=Table1[Product] && EARLIER(Table1[Date])>Table1[Date])))

DateDiff-PC = DATEDIFF('Table1'[Previous-PC],'Table1'[Date],DAY)

Best Regards,
Qiuyun Yu

[erezbenmoshe]

Worked perfectly! thanks

[wfiore]

Hi @v-qiuyu-msft,

I realize this is an old post. Can you tell me if the calculated columns are supposed to work as intended if the Date field contains non-unique values. I do not seem to be getting accurate results and I suspect I am missing an agreggator due to the fact that my Date column is not unique.

Thank you

Bill