DAX to calculate future booked appointments

[ryan_b_fiting]

Hello Community - 

I am trying to figure out how to write a measure to calculate the Total Future Appointments Booked for my providers.  I am displaying in a table view by patient, so when a provider looks at the report, they see their schedule for the next N days (7 days, 1 month etc) based on the relative date slice they select by patient.

What I need to include in that table is a measure that will show them how many FUTURE appointments that patient has booked beyond the max date of that relative slicer.

Here is a sample of data and an expected output:

AppointmentID	Appointment Date	Patient ID	Appt Type
1	10/1/2022	11	Acu
2	11/1/2022	11	Acu Wellness
3	12/1/2022	11	Acu
4	10/21/2022	22	Acu 30
5	10/31/2022	33	Acu 30
6	11/15/2022	22	Acu 30
7	12/1/2022	22	Wellness
8	12/22/2022	33	Wellness
9	1/3/2023	22	Acu 60
10	1/9/2023	33	Wellness

Expected Output:  Future Booked Appointments and Next Appt Type are what I am trying to calculate.

Date Slicer 10/1/22 - 10/31/22
Patient ID	Future Booked Appointments	Next Appt Type
11	2	Acu Wellness
22	3	Acu 30
33	2	Wellness

Then if they switch the relative date slicer, the future bookings and Next Appt Type will adjust accordingly.

Any help on this would be greatly appreciated, I have been wrapped up in this for the last few days with no luck and I have a call with my providers later today where I am hoping to show the new report!

Thanks for all the help!

Ryan F

[sevenhills]

If you dont have date table and only one table, you can try this

Next Appointment Type = 
VAR _d = LASTDATE(Table1[Appointment Date]) 
VAR _P = SELECTEDVALUE(Table1[Patient ID])
VAR _FirstApptID = CALCULATE(
	Min(Table1[AppointmentID]),
    FILTER( all(Table1), Table1[Appointment Date] > _d && Table1[Patient ID] = _P ) 
    )
RETURN CALCULATE( CONCATENATEX(
                VALUES(Table1[Appt Type]), [Appt Type],","), 
                Filter(all(Table1), Table1[AppointmentID] = _FirstApptID)) 

Future Booked Appointments = 
VAR _d = LASTDATE(Table1[Appointment Date]) 
VAR _P = SELECTEDVALUE(Table1[Patient ID])

RETURN CALCULATE(
	COUNTROWS('Table1'),
    FILTER( all(Table1), Table1[Appointment Date] > _d && Table1[Patient ID] = _P ) 
    )

Output

Note: Recommended to have date table. 

[Mikelytics · Accepted solution]

Hi @ryan_b_fiting 

Please try the following:

Starting Table in POwer Query:

Following data model. I assume that you have a separate date table with a link to the appointment date

as a starting point I create a table visual with patient ID as well as slicers for my time selection. As an example I chose Oct 2022, so max date is 31.10.2022. Please be aware that the IDs have to be Text type or they can be number type but then you need to turn off autom summarization

Now I create my first measure for future appointments and put it intpo the table

NumberOfFutureAppointments = 

var var_LastDateSelected = LASTDATE(DimDate[Date])

RETURN

CALCULATE(
    COUNTROWS(Fact_Appointments),
    ALL(DimDate),
    Fact_Appointments[Appointment Date] > var_LastDateSelected
)

Now I create the emasure for the next appointment Type and add it to the table

NextAppointment = 

var var_LastDateSelected = LASTDATE(DimDate[Date])

var var_NextDateAfterMaxDate =
    CALCULATE(
            FIRSTDATE(Fact_Appointments[Appointment Date]),
            ALL(DimDate),
            Fact_Appointments[Appointment Date] > var_LastDateSelected
        )

RETURN
CALCULATE(
    CONCATENATEX(
        VALUES(Fact_Appointments[Appt Type]),
        [Appt Type],","
    )
    ,
    ALL(DimDate),
    Fact_Appointments[Appointment Date] = var_NextDateAfterMaxDate
)

when I put the slicer on November:

It should also work with daily dates and so on.

The total shows the next appointment over all patients but you can also turn it off if you do not need it.

@ryan_b_fiting  I use CONCATENATEX to cover the situation when on the next available dates there are different appointment types. Look for example on the total for November. on 01.12.22 one of the pateience has Acu and one has wellenes. And because for both patients the next treatment is on the same day the total shows both them.

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.