Forum Discussion
Average Days between Orders
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
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)
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
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
Worked perfectly! thanks
