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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
VahidDM
Super User
Super User

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors