Forum Discussion

bigrods's avatar
bigrods
Helper III
5 years ago
Solved

Count where item between 2 date fields

Hi,

 

Apologies if the title is not clear, I wasn;t sure how to word it!

 

I'm trying to create the following:

 

Patients can have 1 or more referrals to our health service. Each referral has a start date and an end date. If the referral is ongoing, the end date will be blank.

 

In a seperate table is a list of codes entered on their health record with the date of entry. I need to identify how many patients have been coded as either "Consent to treatment" or "Dissent to treatment" within 3 days of the referral start date.

 

It is complicated by the fact someone could have more than 1 referral as I need to identify correctly for each referral - so it needs to relate to the relevant referral start date.

 

Referral table

Pt IDPtIDSiteStart DateEnd Date
123456123456WingA12/04/2115/05/21
987654987654WingF01/05/21 
123456123456WingA17/05/21 

 

Code table

PtIDPtIDSiteCodeDate
123456123456WingAXaJrC - Consent13/04/21
987654987654WingFXaJrE - Dissent01/05/21

 

From the example above, it should identify that 2 referrals have been coded within 3 days (note the 2nd referral for Pt 123456 should not achieve, as the code on 13th April should relate to the earlier referral only. 

 

Many thanks for any light anyone could shed!

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    Hi bigrods ,

     

    According to your description, do you want to find the number of referrals that meet the starting time? I tried to do a test, refer to the following measure.

    M =
CALCULATE (
    COUNTROWS ( 'Referral table' ),
    FILTER (
        ALL ( 'Referral table' ),
        'Referral table'[Pt ID] = MAX ( 'Referral table'[Pt ID] )
            && MAX ( 'Referral table'[Pt ID] ) = MAX ( 'Code table'[PtID] )
            && 0
                <= ( 'Code table'[Date].[Day] - MAX ( 'Referral table'[Start Date].[Day] ) ) <= 3
    )
)


    If I fail to solve your problem, it may be that I did not fully understand what you mean. Can you describe detailed requirements and desired results? I will answer for you as soon as possible, looking forward to your reply.

     

    Best Regards,
    Henry

     

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

2 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi bigrods ,

     

    According to your description, do you want to find the number of referrals that meet the starting time? I tried to do a test, refer to the following measure.

    M =
CALCULATE (
    COUNTROWS ( 'Referral table' ),
    FILTER (
        ALL ( 'Referral table' ),
        'Referral table'[Pt ID] = MAX ( 'Referral table'[Pt ID] )
            && MAX ( 'Referral table'[Pt ID] ) = MAX ( 'Code table'[PtID] )
            && 0
                <= ( 'Code table'[Date].[Day] - MAX ( 'Referral table'[Start Date].[Day] ) ) <= 3
    )
)


    If I fail to solve your problem, it may be that I did not fully understand what you mean. Can you describe detailed requirements and desired results? I will answer for you as soon as possible, looking forward to your reply.

     

    Best Regards,
    Henry

     

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

    • bigrods's avatar
      bigrods
      Helper III
      5 years ago

      Hi Henry,

       

      Many apologies for not replying; I got caught up with stuff, then it just went out of my head - your solution worked for me, thanks so much!