Create a compound Filter for a prompt date

[STIBBS_807]

I have a table that is a direct query.  I want to review the data based on a date that the user will select.  The data will return the information that has a start date on or after the input date but does not have an end date or the end date is the date selected or greater than the date selected. I am basiclly looking for a file that has an AS of DATE.

If the Selected Date is equal to or less than the AS of Date and the End Date on the file is blank or the End Date is equal to or greater than the AS of Date then give me the records.

Can a compound filter be created in Power BI to do this logic?

[Anonymous · Accepted solution]

Hi @STIBBS_807 

Here's a idea for your reference:

You can create a calendar table for your date selection:

DateSelection = CALENDAR(MIN('YourTable'[StartDate]), MAX('YourTable'[EndDate]))

Then add a measure for the selected date:

SelectedDate = SELECTEDVALUE(DateSelection[Date])

Finally, create a measure to filter the data based on the criteria you provided:

FilteredData = 
CALCULATE(
    [YourMeasure],
    FILTER(
        YourTable,
        YourTable[StartDate] <= [SelectedDate] &&
        (
            ISBLANK(YourTable[EndDate]) || 
            YourTable[EndDate] >= [SelectedDate]
        )
    )
)

At last, you can add a slicer to your report for the DateSelection table, so the user can pick a specific date, and use the measure in the visual.

Best Regards

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

[HamedM1125]

@Anonymous  One thing I would like to add is when the value of a date is blank the slicer (with a slider) will skip it, unless the blank value is replaced with a future date in the Power Query.