Custom Measure: Is date between or later than two dates

[AW1976NOVA]

Hi,

I need to create a custom measure that will find the distinct count of [Member ID] if [Term Date] is between the min and max of [The Date]...or if [Term Date] is later than the max of [The Date]

I have two tables:

The MembersTable, where [Member ID] and [Term Date] exist

DateTable, where [The Date] exists

I have a slicer visualization where I'm using DateTable[The Date].

This is what I came up with:

CALCULATE (

DISTINCTCOUNT ( DM_IHA_Member[MemberID] ),

DATESBETWEEN ( DM_IHA_Member[TermDate], MIN ( Nova_Ancillary_VW_CalendarAsOfToday[TheDate] ), MAX ( Nova_Ancillary_VW_CalendarAsOfToday[TheDate] ) ) ||

DM_IHA_Member[TermDate] >= MAX ( Nova_Ancillary_VW_CalendarAsOfToday[TheDate] ) )

But, the || portion is not working.  I need to properly incorporate the OR functionality.

[VahidDM]

Hi @AW1976NOVA 

Can you add more details, or sample of your data and result?

It seems your IF logit has issue!

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[AW1976NOVA]

I have a relationship between DateTable and MembersTable.  It is using DateTable[TheDate] -> MembersTable[RunDate]

DateTable Example Data =

[TheDate]

1/1/2020

1/2/2020

1/3/2020

1/4/2020

1/5/2020

1/6/2020

1/7/2020

1/8/2020

...

...

7/20/2022

MemberTable Data Example = 

[Run Date]            [Member ID]            [TermDate]

1/1/2020               0001                        1/3/2020

1/2/2020               9988                        1/7/2020

1/2/2020               5555                        1/2/2020

1/2/2020               4444                        10/19/2005

1/3/2020               3333                        3/3/2022

1/4/2020               1111                        10/10/2025

1/5/2020               2222                        2/17/2021

If I had my date slicer visualization moved to 1/1/2020 to 2/10/2022...I would expect the custom measure to return a count of 6 [Member ID] because Member ID 0001, 9988, 5555, 3333, 1111, and 2222 have their [Term Date] that fall between the two slicer values OR their [Term Date] is greater than or equal to the max value of the date slicer visual

[v-zhangti · Accepted solution]

Hi, @AW1976NOVA 

You can try the following methods.

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'DM_IHA_Member'[Member ID] ),
    FILTER (
        ALL ( 'DM_IHA_Member' ),
        OR (
            [TermDate] >= MIN ( 'Date'[Date] )
                && [TermDate] <= MAX ( 'Date'[Date] ),
            [TermDate] >= MAX ( 'Date'[Date] )
        )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[AW1976NOVA]

I also tried this:

Test =

VAR _Min = CALCULATE ( MIN ( 'Nova_Ancillary_VW_CalendarAsOfToday'[TheDate] ) )

VAR _Max = CALCULATE ( MAX ( 'Nova_Ancillary_VW_CalendarAsOfToday'[TheDate] ) )

RETURN

CALCULATE (

DISTINCTCOUNT ( DM_IHA_Member[MemberID] ), ( DATESBETWEEN ( DM_IHA_Member[TermDate], _Min, _Max ) && DM_IHA_Member[TermDate] >= _Max ) )

...but I got an error saying that "A function 'DATEBETWEEN' has been used in a True/False expression that is used as a table filter expression.  That is not allowed"