Powerpivot dax filtering rows based on row context date comparison

0
votes

I'm trying to determine how many return appointments each customer had within a set timeframe from their last appointment. I need to identify per row whether that particular appointment is a 'return' appointment within 48hrs of them leaving their last appointment (i.e. was their appointment start time within 48hrs of their prior appointment end time)?

example data

I have tried to find an answer but I'm not really sure what I'm looking for and am relatively new to dax so apologies if this is something basic. I can do the first part relative to the latest appointment as it is simply a max of all the appointments for that customer.

=CALCULATE(max([End Time]),ALLEXCEPT(Table,Table[Customer ID]))

What I'm struggling with is how to incorporate the extra filter to only look at prior appointments (relative to each row). I'm assuming that if I could return a table that was filtered to only show those prior records I could then get the max and compare that but not sure how to go about writing that in dax.

Many thanks for any assistance.

Cheers, Chris.

1
powerpivotdax

1 Answers

0
votes

Think I cracked it, although I'm not really sure how this formula is working so if anyone can break it down and explain it to me that would be greatly appreciated!

1st step: Calculate (at a row context level) the most recent prior appointment end time

=CALCULATE(Max([end time]),ALLEXCEPT(Table1,Table1[customer ID]),EARLIER(Table1[start time])>Table1[start time])

2nd step: Calculate the time difference between the current appointment and most recent end time

=IF(NOT(ISBLANK([Most Recent Prior End Time])),1.*[end time]-[Most Recent Prior End Time],BLANK())

I know how the time difference is working, but not really how the earlier function works in calculating the end time of the most recent appointment.

final table result