Forum Discussion
Tomas1212
6 years agoNew Member
Average between dates
Hi I'm trying to calculate the average days elapsed ("orderdate" column) between order to order, for every customer. How can i do it?
mahoneypat
6 years agoMicrosoft Employee
I would suggest you do this with a calculated column in your order table with an expression like this
Days Since Last Order =
VAR thisdate = Orders[OrderDate]
VAR lastorderdate =
CALCULATE (
MAX ( Orders[OrderDate] ),
ALLEXCEPT ( Orders, Orders[Customer] ),
Orders[OrderDate] < thisdate
)
RETURN
DATEDIFF ( lastorderdate, thisdate, DAY )
Once you have that, you can simply use this as a measure to get your result
Avg Days Between Orders = AVERAGE(Orders[Days Since Last Order])
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat