How to write a measure in power bi to count items based on their status as of selected date

Question

I cannot think of a solution for this: I have a table with the following columns: Employee id, hired date, fired date, current_status.
current_status is a conditional column showing status of employee for a current date i.e. if fired date is blank the status is "working", else the status is "fired".

I want to create a measure, which would show count of employees by their status as of end of selected month.

E.g an employee's current status may be "fired", but as of end of June 2019 his status was "working", so in the context of previous date he should be counted as working.

Kosuke Sakai Kosuke Sakai · Accepted Answer · 2019-12-22T20:14:11

You can use an iterator function, such as FILTER or SUMX, to decide the status of each employee based on the dates.

Here is an example to count the number of working employees at the ending date of the currently displayed period.

Working Employees Count = 
COUNTROWS (
    FILTER (
        Employee,
        Employee[Hired Date] <= MAX ( 'Calendar'[Date] ) &&
        (
            ISBLANK ( Employee[Fired Date] ) ||
            Employee[Fired Date] > MAX ( 'Calendar'[Date] )
        )
    )
)

How to write a measure in power bi to count items based on their status as of selected date

2 Answers