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

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