Average frequency between events

[paivers]

I have a simple table of customer IDs and event dates. I want to measure the average days between events for each customer, and average them for specific groups. I have a date dimension. I get the feeling I need to calculate with an all filter. I would take all events within the selected time range and find the min/max (since you cannot assume a frequency outside of that), count up the events in that range and divide the days in the range by the number of events minus one - e.g. 3 events, take the days between the first and last / 2. The problem I am running into is that if I take all events in the range, it will be for all customers, and so very high frequency. I need to force it to calculate for each customer first, then aggregate. Has anyone done anything like this or have any ideas.

 

[julianwi]

I have a simple table of customer IDs and event dates. I want to measure the average days between events for each customer, and average them for specific groups. I have a date dimension. I get the feeling I need to calculate with an all filter. I would take all events within the selected time range and find the min/max (since you cannot assume a frequency outside of that), count up the events in that range and divide the days in the range by the number of events minus one - e.g. 3 events, take the days between the first and last / 2. The problem I am running into is that if I take all events in the range, it will be for all customers, and so very high frequency. I need to force it to calculate for each customer first, then aggregate. Has anyone done anything like this or have any ideas.

Hi Paivers,
Do you really want just to divide the first and the last date by the number of events?
I am not sure if I understood correctly but I could imagine the following solution:
I would have the customer id on my pivot-table and the date-filter (e.g. a certain month) as a slicer.
In order to calculate the number of events per customer:
NoOfEvents:=calculate(countx(events;events[customer id]))
And to calculate the distance between the first and the last event according to the current filter:
Distance:=calculate(
IF([NoOfEvents]=0;blank();
countx(
DATESBETWEEN(Datum[Date];FIRSTDATE(events[event date]);lastdate(events[event date]));
Datum[date])))
The relevant measure would just be the division
Average Frequency:=DIVIDE([Distance];[NoOfEvents])
I hope I understood correctly. Regards julianwi

 

[paivers]

Yes, that looks about right, the DATESBETWEEN() was what I was missing. I do think I would divide the days between first and last by the number of events-1 to get the average gap, so if 3 events (first, middle, last), I divide by 2 to get the first half and last half. Thanks Julianwi, I'll give it a try.