Forum Discussion

AmandaHore's avatar
AmandaHore
Helper I
8 months ago
Solved

Assistance with Date Filters

We have a vendor supplied database to record people's time entries, among other things.  It has a table called User Posting that shows if they are currently employed, their pay rate and the hours they are expected to work in a week.   We use this to calculate if they have worked their hours for a given pay period. 

The table is similar to below.   I normally filter when the end date is blank, and that means they are active.  It works great.  When someone leaves we put in an end date.  They then do not appear on our report for that given fortnight, as the value is no longer blank.  

 

Person Start Date End Date
Mary1/10/2025 
Joe1/01/202514/12/2025
Peter1/06/2023 
John1/08/2023 

 

I have a date slicer in the report for the pay period

This is the current filter I use.  I need a way to have this filter show is blank, or is in the values of the date slicer.  

If that is not possible need a way to show is blank, or in the last x days.  

 

Is there any way I can apply these to filters in Power BI Desktop report.  

 

Need Help
  • Praful_Potphode's avatar
    Praful_Potphode
    8 months ago

    Hi AmandaHore ,

    You can create a measure with output as 0 or 1 for your conditions and then use it as a visual-level filter:

    Is Active in Period = 
VAR PeriodStart = MIN('Date'[Date])  // The start date from your slicer
VAR PeriodEnd   = MAX('Date'[Date])  // The end date from your slicer
VAR EmpStart    = SELECTEDVALUE('User Posting'[Start Date])
VAR EmpEnd      = SELECTEDVALUE('User Posting'[End Date])

RETURN
// Logic: An employee is active if they started before the period ended...
// ...AND (they are still active OR they left after the period started).
IF (
    EmpStart <= PeriodEnd && 
    (ISBLANK(EmpEnd) || EmpEnd >= PeriodStart),
    1,
    0
)

    Please give kudos or mark it as a solution once confirmed.

     

    Thanks and Regards,

    Praful

6 Replies

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    8 months ago

    Hi AmandaHore ,

    You can create a measure with output as 0 or 1 for your conditions and then use it as a visual-level filter:

    Is Active in Period = 
VAR PeriodStart = MIN('Date'[Date])  // The start date from your slicer
VAR PeriodEnd   = MAX('Date'[Date])  // The end date from your slicer
VAR EmpStart    = SELECTEDVALUE('User Posting'[Start Date])
VAR EmpEnd      = SELECTEDVALUE('User Posting'[End Date])

RETURN
// Logic: An employee is active if they started before the period ended...
// ...AND (they are still active OR they left after the period started).
IF (
    EmpStart <= PeriodEnd && 
    (ISBLANK(EmpEnd) || EmpEnd >= PeriodStart),
    1,
    0
)

    Please give kudos or mark it as a solution once confirmed.

     

    Thanks and Regards,

    Praful

    • AmandaHore's avatar
      AmandaHore
      Helper I
      8 months ago

      Thanks, it got me to the next step in my solution.  It was what I needed

  • Irwan's avatar
    Irwan
    Super User
    8 months ago

    hello AmandaHore 

     

    when you said "is in the values of the date slicer", do you want to show only the blank AND selected date slicer?

    i might be misunderstood but from your description, when someone leaves then you put a date in your table (end date i assumed). What date you want to show? is it start date or end date?

     

    please show what is your desired outcome from your sample above.

     

    Thank you.

  • AmandaHore's avatar
    AmandaHore
    Helper I
    8 months ago

    I want the query to be an OR query.  Query show everything that is Blank, if not blank must be in the range of the date slicer.  

    I am using the returned values then for another query so hard to show the end result.  

  • Abhilash_P's avatar
    Abhilash_P
    Super User
    8 months ago

    Hi AmandaHore 

     

    You cannot directly use a filter like “End Date is blank OR End Date is in the slicer selection” in the Filters pane. Slicer values are dynamic, and filters don’t support that logic.

     

    use a DAX measure and filter on that.

     

     

     

     

    Recommended solution (works with a date slicer)

    Below is the sample DAX for the measure

     

    Show Active Employee =

    VAR PeriodStart = MIN ( 'Date'[Date] )

    VAR PeriodEnd   = MAX ( 'Date'[Date] )

    VAR EndDt       = MIN ( 'User Posting'[End Date] )

    VAR StartDt     = MIN ( 'User Posting'[Start Date] )

    RETURN

    IF (

        StartDt <= PeriodEnd &&

        ( ISBLANK ( EndDt ) || EndDt >= PeriodStart ),

        1,

        0

     

     

    Use as below

     

     

    • Add this measure to the visual or page filters
    • Set filter to Show Active Employee = 1

     

     

    This will:

     

    • Include employees with blank End Date
    • Include employees whose End Date falls within the selected pay period
    • Exclude employees who left before the selected period

     

    Thanks

     

  • krishnakanth240's avatar
    krishnakanth240
    Super User
    8 months ago

    Hi AmandaHore 

    Yes, this is possible, but not directly with a visual-level filter UI. You are hitting a very common Power BI limitation: Filters cannot natively do “Is Blank OR In Slicer Range”

    So we need to replace the filter with a DAX-based logic, using either a measure filter or a calculated column.

     

    > Measure-based filter

    Show a person when:
    End Date is BLANK (still active), OR
    End Date falls inside the selected pay period

    You must have a proper Date table
    If not, create it:
    Date =
    CALENDAR ( DATE(2020,1,1), DATE(2030,12,31) )


    Mark it as Date table and relate it to:
    User Posting[End Date] (inactive relationship is fine)

     

    Create measure
    Show Person =
    VAR StartDate =
    MIN ( 'Date'[Date] )

    VAR EndDate =
    MAX ( 'Date'[Date] )

    VAR PersonEndDate =
    SELECTEDVALUE ( 'User Posting'[End Date] )

    RETURN
    IF (
    ISBLANK ( PersonEndDate )
    || ( PersonEndDate >= StartDate && PersonEndDate <= EndDate ),
    1,
    0
    )

     

    Apply it as a Visual-level filter
    Drag Show Person into Filters on this visual

    Set:
    Show Person = 1


    Works with slicers
    Handles blanks
    Fully dynamic per pay period


    Alternative: “Last X Days” version

    If you want:
    Show active OR ended in last X days (e.g., 14)

    Show Person (Last X Days) =
    VAR TodayDate = TODAY()
    VAR XDays = 14
    VAR PersonEndDate = SELECTEDVALUE ( 'User Posting'[End Date] )

    RETURN
    IF (
    ISBLANK ( PersonEndDate )
    || PersonEndDate >= TodayDate - XDays,
    1,
    0
    )