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

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!