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 the...
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

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