Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
AW1976NOVA
Post Patron
Post Patron

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.
1 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] )
        )
    )
)

vzhangti_2-1658743414452.png

vzhangti_1-1658743363347.png

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.

View solution in original post

4 REPLIES 4

Hi @AW1976NOVA 

 

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

 

It seems your IF logit has issue!

 

 

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

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

 

Capture.PNG

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] )
        )
    )
)

vzhangti_2-1658743414452.png

vzhangti_1-1658743363347.png

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.

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"

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.