Search code examples
powerbidaxpowerbi-desktopmeasure

Show (count) and filter active employees based on date slicer

what i am trying to achieve is the following,

i have a (flat) table with employees containing first name,last name, hire date and termination date.

i would like to filter the table and also count the active ones based on a date slicer.

January 2022 - 200 total employees - 190 active employees - 10 terminated employees

the issues that i am facing is that if an employee was terminated on 01/10/2022 and I choose the date of 01/09/2022, that employee should appear on the list because he was active on that date.

i m coming from this topic https://community.powerbi.com/t5/Desktop/List-of-active-employees-on-a-date/td-p/1609370 -- but i do not have a status of Active/Terminated,just dates.

any thoughts?

Solution

  • If you want to count emp status for end of period, use a measure:

    enter image description here

    CountOfActive = 
var _selectedDate = MAX('Calendar'[Date])
return
CALCULATE(COUNTROWS('employee'), filter(ALL(employee), employee[Hire Date] <= VALUE(_selectedDate) && (employee[Termination Date] >= VALUE(_selectedDate) || ISBLANK(employee[Termination Date]))))

CountOfTerminated = 
var _selectedDate = MAX('Calendar'[Date])
return
CALCULATE(COUNTROWS('employee'), filter(ALL(employee), employee[Hire Date] <= VALUE(_selectedDate) && (employee[Termination Date] < VALUE(_selectedDate) )))