Finding the date between ranges

Hi Praj ,

I want to confimr with you: there are several days which are between 90 and180 days. For example, for name aa, 6/1 and 7/21 are both in 90-180 days. Which day is the result you want? Max or Min ? In my sample file, I get the Min day which are between 90 and 180 days. You can adjust it according  to the result you want. My pbix file is just for your reference.

Please create a new table:

NewTable = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "Name", 'Table'[Name],
        "EventID", 'Table'[Event ID],
        "Date Registered", 'Table'[Date Registered],
        "Event Date", 'Table'[Date of Events attended]
    ),
    SELECTCOLUMNS (
        'Table',
        "Name", 'Table'[Name],
        "EventID", 'Table'[Event ID],
        "Date Registered", 'Table'[Date Registered],
        "Event Date", 'Table'[First attended event by user]
    )
)

You will get a table like this:

Create two measures:

Event attend 0-90 days =
VAR _a =
    SELECTEDVALUE ( 'NewTable'[Date Registered] )
VAR _b =
    CALCULATE (
        MIN ( 'NewTable'[Event Date] ),
        FILTER (
            'NewTable',
            'NewTable'[Name] = SELECTEDVALUE ( 'NewTable'[Name] )
                && 'NewTable'[Event Date] <= _a + 90
        )
    )
VAR _c =
    DATEDIFF ( _a, _b, DAY )
VAR _d =
    IF ( _c = BLANK (), "No", _b )
RETURN
    IF ( ISINSCOPE ( 'NewTable'[Name] ), _d, BLANK () )



Event attend 90-180 days =
VAR _a =
    SELECTEDVALUE ( 'NewTable'[Date Registered] )
VAR _b =
    CALCULATE (
        MIN ( 'NewTable'[Event Date] ),
        FILTER (
            'NewTable',
            'NewTable'[Name] = SELECTEDVALUE ( 'NewTable'[Name] )
                && 'NewTable'[Event Date] > _a + 90
                && 'NewTable'[Event Date] <= _a + 180
        )
    )
VAR _c =
    DATEDIFF ( _a, _b, DAY )
VAR _d =
    IF ( _c = BLANK (), "No", _b )
RETURN
    IF ( ISINSCOPE ( 'NewTable'[Name] ), _d, BLANK () )

You will get the result you want:

Best regards,

Yadong Fang

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