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

4 Replies

    • 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"