Forum Discussion

ryan_b_fiting's avatar
ryan_b_fiting
Post Patron
3 years ago
Solved

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

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:

ProviderIDPatientIDAppointmentIDAppointmentDateAppointmentType
111121/2/2023Acu-Normal
111131/9/2023Acu-Normal
111141/16/2023Acu-Normal
111151/23/2023Acu-Normal
152161/8/2023Acu-Specialty
187211/22/2023Acu-Specialty

 

ENCOUNTERS TABLE:

ProviderIDPatientIDEncounterIDEncounterDateEncounterType
111712/21/2022Acu NPE
152812/23/2022Acu NPE
111912/23/2022Acu-Specialty
1871012/23/2022Acu-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:

ProviderIDEncountersNPEsBooked/Completed Post NPE
11215
15111
18100

 

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

Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      ryan_b_fiting
      Post Patron
      3 years ago

      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's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      ryan_b_fiting
      Post Patron
      3 years ago

      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

      ProviderIDPatientIDAppointmentIDAppointmentDateAppointmentType
      111121/2/2023Acu-Normal
      111131/9/2023Acu-Normal
      111141/16/2023Acu-Normal
      111151/23/2023Acu-Normal
      152161/8/2023Acu-Specialty
      187211/22/2023Acu-Specialty
      1512221/23/2023Acu-Specialty

       

      ENCOUNTERS:

      ProviderIDPatientIDEncounterIDEncounterDateEncounterType
      111712/21/2022Acu NPE
      152812/23/2022Acu NPE
      111912/23/2022Acu-Specialty
      1871012/23/2022Acu-Normal
      1131112/24/2022Acu-Normal
      1141212/24/2022Acu-Specialty

       

      EXPECTED OUTPUT:

      ProviderIDEncountersNPEsBooked/Completed Post NPE
      11415
      15111
      18100

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