Filter a table with containing date ranges using an entered value from date table

[BeAStar]

I'm trying to filter a table (table 1) with an input date, tried using a date table (table 2) for the input (range date) but I'm not able to find a way to do it. Help would be appreciated very much.

This is what I tried but it doesn't work, the selectedDate is a messure in Table1

[Jai-Rathinavel · Accepted solution]

@BeAStar Create a InRange measure with the DAX expression below

InRange = 
var sel = SELECTEDVALUE('Calendar'[SelectDate])
var result = 
    IF(
        AND(MAX(Contract[ServiceLifeStartDate]) <= sel,
            MAX(Contract[ServiceLifeEndDate]) >= sel),
            "Yes",IF(
                  ISBLANK(sel),BLANK(),"No")
        )
RETURN
result

Did I answer your question ? If yes, please mark my post as a solution.

Thanks,

Jai

[BeAStar]

Thank you so much!! was not getting the results needed but with suggestions IT WORKED!!!! Thanks Again!!!!

[Anonymous · Accepted solution]

Hi,

Thanks for the solution Jai-Rathinavel  offered, and i want to offer some more informatiorn for user to refer to.

hello @BeAStar , you can refer to the following soltuion.

Sample data is the same as you privided.

Create a calendar table., and create 1:n relationship between tables.（date->startdate)

Calendar = CALENDAR(DATE(2020,1,1),DATE(2025,12,31))

Then create a measure.

MEASURE =
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Start Date] <= MAX ( 'Calendar'[Date] ),
        'Table'[End Date] >= MIN ( 'Calendar'[Date] ),
        CROSSFILTER ( 'Calendar'[Date], 'Table'[Start Date], NONE )
    )
RETURN
    IF ( a > 0, "Yes", "No" )

Then put the date of calendar table to the slicer, and put the measure and the contracts to the table visual.

Output

Best Regards!

Yolo Zhu

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

[BeAStar]

unfortunately it didn't work

[Jai-Rathinavel]

@BeAStar it should work remove the relationship between table 1 and table 2. I tested it on my file and provided the output.

[Jai-Rathinavel · Accepted solution]

@BeAStar Create a InRange measure with the DAX expression below

InRange = 
var sel = SELECTEDVALUE('Calendar'[SelectDate])
var result = 
    IF(
        AND(MAX(Contract[ServiceLifeStartDate]) <= sel,
            MAX(Contract[ServiceLifeEndDate]) >= sel),
            "Yes",IF(
                  ISBLANK(sel),BLANK(),"No")
        )
RETURN
result

Did I answer your question ? If yes, please mark my post as a solution.

Thanks,

Jai