Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Custom Measure: Is date between or later than two dates

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 Da...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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"