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 p...
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.

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