Average difference between two dates based on two other columns

[Flawn]

Hello Everyone, 

First question here but I've drawn upon this forum for help for a long time. So thanks for all the PBI help up until now - but this one has me stumped and I haven't been able to find the answer; so its finally time to actually ask!

We have three columns relevent to our problem: 1. A Client ID column. 2. An appointment classification column, which for our purposes has two relevent buckets; 'service requests' and 'other'. 3. A Appointment creation date column.

We need to calculate the average difference in days between a service request appointment and the next  'other' appointment a client attends.

I am finding it very difficult to construct a formula that can keep track of the clients as well as identifying which is the 'next' appointment after the service request without any other identifiers (which, unfortunately, I am unable to add due to the way their database has been constructed). Perhaps the solution is simpler than my brain is making it; so i'll pass it on to other, more capable minds! The dataset is very large and confidential so i've constructed a simplified example of the data in question below and 'color coded' the pairings that should be matched and have their differences calculated for the average to better illustrate the objective.

Client ID	Appointment Category	Date
1	Service Request	01/06/2020
1	Meeting	09/06/2020
1	Education	07/06/2020
1	Meeting	05/06/2020
2	Meeting	02/06/2020
2	Service Request	06/06/2020
2	Education	11/06/2020
2	Education	15/06/2020
3	Service Request	4/06/2020
3	Meeting	9/06/2020
3	Service Request	14/06/2020
3	Education	19/06/2020

Of course, I am happy to elaborate if i was unclear on anything or if I missed some edge case that could effect the answer!

Thank you all so much in advance for your time!

Regards,
-Flawn

[amitchandak]

@Flawn , what is expected output?

You can try a new column like

if([Appointment Category] = " Service Request" ,
datediff( [Date], Minx(filter(Table, [Date] > earlier([Date]) && [Appointment Category] = "Meeting" && [Client ID] = earlier([Client ID])),[Date]), day()),
blank())

[Flawn]

Thank you amitchandak for your quick response!

The expected output should be the difference, in days, between a service request appointment and the next non-service request appointment that client attends. After which the average difference is simple enough to calculate.

In the example case I provided, the expected output would be a 5 day difference for the blue, yellow, and green pairing. And a 4 day difference for the red pairing. Which would then average out to 4.75 days.

I tried your column and I am unable to verify whether or not it would work as, unfortunately, it seemed to demand too much memory use, producing a "There's not enough memory to complete this operation" error when i tried to introduce this column. This is the first time i've come across this error with this particular database. I've taken steps to reduce the memory burden Including unloading as many queries as possible, removing extraneous columns (there weren't many I could afford to remove), removing background loading of tables and upping the max allowed data cache - but the problem persists. . Can we produce a solution that is less demanding on the memory - or is there perhaps some way to fix this problem I am unfamiliar with?