Count where item between 2 date fields

[bigrods]

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 ID	PtIDSite	Start Date	End Date
123456	123456WingA	12/04/21	15/05/21
987654	987654WingF	01/05/21
123456	123456WingA	17/05/21

Code table

PtID	PtIDSite	Code	Date
123456	123456WingA	XaJrC - Consent	13/04/21
987654	987654WingF	XaJrE - Dissent	01/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!

[v-henryk-mstf · Accepted solution]

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]

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!