Calculate 3rd Opening

[TabathaN]

Hello,

I work for a medical practice and we are looking to create a table/matrix that shows the 3rd next opening on a providers schedule. I am at a loss on how to accomplish this and could really use some help. 

Of the data below, I would expect a returned result of:

Dr A 3rd next opening = 01/03/2020

Dr A 3rd next new patient opening = 03/02/2020

Patient ID	Appointment Date	Appointment Status	Scheduling Provider	Appointment Type
328775	1/1/2020	f - Filled	Dr A	New Patient
419104	1/1/2020	f - Filled	Dr A	New Patient
	1/1/2020	o - Open Slot	Dr A	New Patient
162981	1/1/2020	f - Filled	Dr A	New Patient
432123	1/2/2020	f - Filled	Dr A	Follow Up
432123	1/2/2020	x - Cancelled	Dr A	Follow Up
	1/2/2020	o - Open Slot	Dr A	Follow Up
209521	1/2/2020	f - Filled	Dr A	Follow Up
288203	1/3/2020	f - Filled	Dr A	Study
	1/3/2020	o - Open Slot	Dr A	Study
334641	1/3/2020	f - Filled	Dr A	Study
298421	1/3/2020	f - Filled	Dr A	Study
	1/10/2020	o - Open Slot	Dr A	Follow Up
	1/15/2020	o - Open Slot	Dr A	Study
	1/20/2020	o - Open Slot	Dr A	New Patient
	3/2/2020	o - Open Slot	Dr A	New Patient
	2/1/2020	o - Open Slot	Dr B	New Patient
454121	2/1/2020	f - Filled	Dr B	Follow Up
	2/2/2020	o - Open Slot	Dr B	Study
245991	2/2/2020	f - Filled	Dr B	New Patient
333813	2/3/2020	f - Filled	Dr B	Follow Up
186858	2/3/2020	f - Filled	Dr B	Study
	2/10/2020	o - Open Slot	Dr B	Study
	2/15/2020	o - Open Slot	Dr B	New Patient
	2/20/2020	o - Open Slot	Dr B	Follow Up
	2/25/2020	o - Open Slot	Dr B	Study
	2/28/2020	o - Open Slot	Dr B	New Patient
	4/5/2020	o - Open Slot	Dr B	New Patient
	4/30/2020	o - Open Slot	Dr B	Follow Up
	2/3/2021	o - Open Slot	Dr B	Follow Up
	3/1/2020	o - Open Slot	Dr C	New Patient
339339	3/1/2020	f - Filled	Dr C	Follow Up
	3/2/2020	o - Open Slot	Dr C	Study
394545	3/2/2020	f - Filled	Dr C	New Patient
	3/3/2020	o - Open Slot	Dr C	Follow Up
256215	3/3/2020	f - Filled	Dr C	Study
250682	3/3/2020	f - Filled	Dr C	New Patient
	3/10/2020	o - Open Slot	Dr C	Follow Up
	3/15/2020	o - Open Slot	Dr C	Study
	3/20/2020	o - Open Slot	Dr C	New Patient
	3/25/2020	o - Open Slot	Dr C	Follow Up
	5/1/2020	o - Open Slot	Dr C	Study
	5/15/2020	o - Open Slot	Dr C	New Patient
	5/31/2020	o - Open Slot	Dr C	Follow Up

[kentyler]

Hello,

While I was working on your question Power BI produced one of its marvelous surpsizes
I added a Doctor dimension and a Status dimension table

Then I put the appoint table on a report and created a slicer for doctor and a slicer for status

Without adding any DAX you can now filter by doctor and status, or just by doctor, or just by status. This version is more powerful than what you asked for.... but I thought it might be very helpful
If you'd still like a dax measure to get the next 2 open appointments let me know and I can write that for you.

I'm a personal Power BI trainer.  Every time I answer a question I learn something new.

[TabathaN]

thank you @kentyler ! I definitely will use the slicer functionality but here's what I'm thinking...

I would like the visual to list the singular record of the 3rd next available date rather than users having to look and figure it out.

Provider	3rd Next Avail
Dr A	1/3/2020
Dr B	2/10/2020
Dr C	3/3/2020

And then whether we would need to create a second visual, or add slicer to say "ok, now what is the 3rd next New Patient, not just overall 3rd next". So kinda looking for two things but priority is the overall 3rd next avail. Does that make sense?

Provider	3rd Next New Patient
Dr A	3/2/2020
Dr B	2/28/2020
Dr C	5/15/2020

[v-juanli-msft · Accepted solution]

Hi @TabathaN 

Create three measures

Measure = MAX('Table'[Appointment Date])

3rd Next Avail =
CALCULATE (
    MAX ( 'Table'[Appointment Date] ),
    TOPN (
        3,
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Scheduling Provider] = MAX ( 'Table'[Scheduling Provider] )
                && FIND ( "Open", 'Table'[Appointment Status], 1, 0 ) > 0
        ),
        [Measure], ASC
    )
)


3rd Next New Patient =
CALCULATE (
    MAX ( 'Table'[Appointment Date] ),
    TOPN (
        3,
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Scheduling Provider] = MAX ( 'Table'[Scheduling Provider] )
                && FIND ( "Open", 'Table'[Appointment Status], 1, 0 ) > 0
                && 'Table'[Appointment Type] = "New Patient"
        ),
        [Measure], ASC
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kentyler]

OK

So if the user selects a Doctor who as 6 future openings, you want a single card that displays the date of the 3rd of those openings, but not the 1st or 2nd ?

[TabathaN]

@kentyler correct, no need to know what the other openings are - just need the 3rd.