How to set up 1 slicer filtering on 2 conditions?

[Anonymous]

Hello, I could use some help with the following problem: I have a table with contracts. The table has two columns "Start Date" and "End Date", where the start date is always less than the end date. The end date is an optional field, and is blank for most of the contracts.

I would now like to filter the table with a slicer for a key date. With this slicer, all contracts that have already started on the selected date, and have not yet ended, should be displayed in my report.

Putting a date filter on the "Start Date" column, gives me all contracts that have already started on the selected date. But some of them have already ended. How do I have to expand the filter so that ended contracts are suppressed?

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below and apply a visual-level filter on your visual with condition(Flag is 1) just as below screenshot:

Flag = 
VAR _enddate =
    SELECTEDVALUE ( 'Table'[End Date] )
RETURN
    IF ( ISBLANK ( _enddate ) || _enddate >= TODAY (), 1, 0 )

If the above one can't help you get the desired result, please provide some sample data in contract table and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards