Need to find if a selected date range is between two dates

[MagusTheWiser]

Hello,

I searched but could not find a situation like mine.

I have two columns with dates, one is the date of the registration on a course, and the other is the expiration date of that license.

So i need to find if a selected date or interval is between the two dates, so i can find all those who are/were with a active registration on that period.

For example:

In the slicer, If i select 25/03/2023 and 28/03/2023 it should show the seven entries with udemy but not any alura, because the interval is between the registration and expiration date of the udemy, but not enough for the alura to get active.

if i select 23/03/2023 and 30/03/2023 it is going to show all entries, because the period is between the dates in both columns.

if i select 05/05/2023 and 30/06/2023 it is going to show the seven entries again, because its after the expiration of the alura license, but the udemy period is active between the dates. 

Thanks in advance!

[MohammadLoran25 · Accepted solution]

Hi @MagusTheWiser ,

Assume your table name is "RegisExpireTable" and your date table name is "FullDDate" and also there is no relationship betwee these 2 tables. Then Follow Steps Below:

1-Creating a measure as :

IsActiveMeasure =
SUMX (
    RegisExpireTable,
    SWITCH (
        TRUE (),
        MIN ( FullDDate[Date] ) >= MIN ( RegisExpireTable[RegisterDate] )
            && MIN ( FullDDate[Date] ) <= MAX ( RegisExpireTable[ExpireDate] ), 1,
        MAX ( FullDDate[Date] ) >= MIN ( RegisExpireTable[RegisterDate] )
            && MAX ( FullDDate[Date] ) <= MAX ( RegisExpireTable[ExpireDate] ), 1
    )
)

2- Then Put this measure on a filter pane on your visual and set the advanced condition of "IS Greater than 0" on it and apply the filter. Then Based on Your date slicer Selection (from FullDDate Table), this measure would filter your data as you need.

If this answer solves your problem, please give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

Regards,
Loran

[MagusTheWiser]

Thank you Mohammad, 

It worked great!