Calculating Counts within a date range PLUS future dates based on Criteria

[ryan_b_fiting]

Hello Community - 

I have a pretty complex calculation that I am trying to solve, and have been stuck on this for quite a while! 

I am trying to calculate visits that happened within a certain period based on my date slicer PLUS future booked appointments based on specific criteria.

My data model includes a DATE_TABLE, APPOINTMENTS (future booked appointments), ENCOUNTERS (visits that have already happened), PROVIDERS tables.

Below is some sample data to give you an idea of what I am trying to do:

APPOINTMENTS TABLE:

ProviderID	PatientID	AppointmentID	AppointmentDate	AppointmentType
11	1	12	1/2/2023	Acu-Normal
11	1	13	1/9/2023	Acu-Normal
11	1	14	1/16/2023	Acu-Normal
11	1	15	1/23/2023	Acu-Normal
15	2	16	1/8/2023	Acu-Specialty
18	7	21	1/22/2023	Acu-Specialty

ENCOUNTERS TABLE:

ProviderID	PatientID	EncounterID	EncounterDate	EncounterType
11	1	7	12/21/2022	Acu NPE
15	2	8	12/23/2022	Acu NPE
11	1	9	12/23/2022	Acu-Specialty
18	7	10	12/23/2022	Acu-Normal

My date table and provider tables are standard dimension tables.

Here is what I am trying to accomplish:

•  I have my date slicer selected for 12/19/22 - 12/25/22
•  This is the date range that is used to determine my encounters that actually occurred and the NPEs that happened during that period.
• I need to be able to take all of the patients that had an NPE (New Patient Exam) during my selected date range, by provider and identify those patients.
• Once those patients are Identified, I need to see how many Completed Visits (Encounters) they had after their intial NPE AND any appointments they have booked into the future, beyond my selected date range.

So with the small sample data set above this is what I would expect to see when I create a table by provider with the Booked/Completed Post NPE measure being the value I am trying to learn how to calculate:

ProviderID	Encounters	NPEs	Booked/Completed Post NPE
11	2	1	5
15	1	1	1
18	1	0	0

Any insights on this would be appreciated.  I have been spinning on this for a little while now and am looking for some expertise on this calculation.

Thank You Community!

Ryan

[Anonymous · Accepted solution]

Hi @ryan_b_fiting ,

You could try these measures.

NPE Date =
CALCULATE (
    MAX ( 'ENCOUNTERS TABLE'[EncounterDate] ),
    FILTER ( 'ENCOUNTERS TABLE', [EncounterType] = "Acu NPE" )
)

NPE Date in Appointments =
CALCULATE (
    MAX ( 'ENCOUNTERS TABLE'[EncounterDate] ),
    FILTER (
        'ENCOUNTERS TABLE',
        [ProviderID] = MAX ( 'APPOINTMENTS TABLE'[ProviderID] )
            && [EncounterType] = "Acu NPE"
    )
)

Booked/Completed Post NPE =
IF (
    [NPEs] <> 0,
    CALCULATE (
        COUNT ( 'ENCOUNTERS TABLE'[ProviderID] ),
        FILTER ( 'ENCOUNTERS TABLE', [EncounterDate] > [NPE Date] )
    )
        + CALCULATE (
            COUNT ( 'APPOINTMENTS TABLE'[ProviderID] ),
            FILTER (
                'APPOINTMENTS TABLE',
                [AppointmentDate] > [NPE Date in Appointments]
                    && [ProviderID] = MAX ( 'ENCOUNTERS TABLE'[ProviderID] )
            )
        ),
    0
)

The two dates are measured by comparing dates with the APPOINTMENTS table and the ENCOUNTERS table, respectively.

Best Regards,

Stephen Tao

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

[ryan_b_fiting]

Thanks @Anonymous for the reply.  How does this work when I have my specific date slicer set?  Are you not joining the APPOINTMENTS table to DATE_TABLE dimension?  I need to be able to slice for the last 1, 6 or 12 weeks of encounters and then look at all future appointments for those new patients as well.

I think this is almost exactly what I need, but need to understand how are we looking at a set date range in the past, but then counting all of the future appointments with your measures.

Thanks
Ryan

[amitchandak]

@ryan_b_fiting , Assuming ProviderID is coming from common dim

Try measures like

M1= calculate(Count(Table1[AppointmentType])  , filter(allselected(Provider), provide[ProviderID] = max( provide[ProviderID] ) )

M2= countx(filter(Table2, Containsstrings(Table2[EncounterType],"NPE")), Table2[ProviderID])

m3= if([M2] <> blank() && [M2]<> 0, [M1],0)

[ryan_b_fiting]

Thanks for the reply @amitchandak but I do not think these measures are what I am looking for. M1 would only count data from the appointments table which is only future bookings, and those appointments do not fall within the sliced date range. 

I would need the future bookings+visits that happened within the sliced date range.

Also, I think I may need to add a few more lines of data to my sample data.  

APPOINTMENTS

ProviderID	PatientID	AppointmentID	AppointmentDate	AppointmentType
11	1	12	1/2/2023	Acu-Normal
11	1	13	1/9/2023	Acu-Normal
11	1	14	1/16/2023	Acu-Normal
11	1	15	1/23/2023	Acu-Normal
15	2	16	1/8/2023	Acu-Specialty
18	7	21	1/22/2023	Acu-Specialty
15	12	22	1/23/2023	Acu-Specialty

ENCOUNTERS:

ProviderID	PatientID	EncounterID	EncounterDate	EncounterType
11	1	7	12/21/2022	Acu NPE
15	2	8	12/23/2022	Acu NPE
11	1	9	12/23/2022	Acu-Specialty
18	7	10	12/23/2022	Acu-Normal
11	3	11	12/24/2022	Acu-Normal
11	4	12	12/24/2022	Acu-Specialty

EXPECTED OUTPUT:

ProviderID	Encounters	NPEs	Booked/Completed Post NPE
11	4	1	5
15	1	1	1
18	1	0	0

Booked or completed Post NPE are ONLY for the patients who had an NPE visit during the selected period