Calculations between rows

[aflintdepm]

I have a table of data that contains a list of appointments with clients.  Some rows include past appointments while other rows contain future appointments.  Here's an example:

Client ID	Client Name	Appointment Date	Appointment Status	Appointment Type
1234	John Doe	1/1/24	Complete	Routine
1234	John Doe	5/1/24	Pending	Routine
5678	Jane Smith	3/1/24	Complete	Routine

As I'm writing this in April 2024, the first appointment is in the past, the second one is in the future

I would like to calculate the time between the 2 appointments to make sure that we are having client contact on the appropriate cadence. 

The third row represents a possible error scenario where the client had a visit in the past, but has no record in the table for the future, so the overall formula would ideally have an IFERROR function to resolve this.

Thank you

[v-cgao-msft · Accepted solution]

Hi @aflintdepm ,

Please try this calculated column:

Time Between Appointments = 
VAR __today =
    TODAY ()
VAR __cur_date = 'Table'[Appointment Date]
VAR __prev_date =
    CALCULATE (
        MAX ( 'Table'[Appointment Date] ),
        'Table'[Appointment Date] < __cur_date
            && 'Table'[Appointment Date] < __today,
        ALLEXCEPT ( 'Table', 'Table'[Client ID] )
    )
VAR __result =
    DATEDIFF ( __prev_date, __cur_date, DAY )
RETURN

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[ryan_mayu]

not sure if this is what you want. 

Column =

VAR _last=maxx(FILTER('Table','Table'[Client ID]=EARLIER('Table'[Client ID])&&'Table'[Appointment Date]<EARLIER('Table'[Appointment Date])),'Table'[Appointment Date])

return DATEDIFF(_last,'Table'[Appointment Date],DAY)

i think your actual data will be more complicated than the sample data you provided

what if the dates are all in the past? what if we have three dates, two in the future and one in the past?

if the solution does not meet your requirements. pls update your sample data and the expected output and make sure your sample data includes the different scenarios as many as possible.