Forum Discussion

STIBBS_807's avatar
STIBBS_807
Resolver I
1 year ago
Solved

Create a compound Filter for a prompt date

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?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      HamedM1125
      Advocate III
      1 year ago

      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.