Forum Discussion

BeAStar's avatar
BeAStar
Frequent Visitor
1 year ago
Solved

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

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. ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.