CODEWITHSUNDEEP
powerbidax
emil
emil

Reputation: 25

Count of active employees between two date columns, by department

I'm trying to imitate this report (page 3) where it slices active headcount and all the other metrics (1) by date and (2) by department.

My data looks like this (with relationships, of course):

ID Name DEPID Hired Date Terminated Date Terminated (Y/N)
1 John 2 1/1/2019 2020/12/31 Y
2 Jane 2 1/3/2018 2019/07/26 Y
3 Jack 1 1/5/2022 null N

Using the following measure, I was able to extract total number of employees by date, but I wasn't able to filter by department:

CountOfActive = 
var _selectedDate = MAX('Calendar'[Date])
return
CALCULATE(COUNTROWS('Table'); filter(ALL('Table'); Table[HIREDDATE] <= VALUE(_selectedDate) && (Table[TERMINATEDDATE] >= VALUE(_selectedDate) || ISBLANK(Table[TERMINATEDDATE]))))

My ideal output is something like the following (where I'll create a table for each department and list the number of active employees, then join them to my department key table afterwards so I can slice them):

Date Count of Active Employees Department
2019/1/1 3 Retail
2019/1/2 3 Retail
2019/1/3 4 Retail
... ... ...

The "Date" column would be a calendar table built with CALENDAR().

What should I do to achieve the last table based on the data I have?

My relationship schema looks like this.

Upvotes: 0

Views: 1693

Answers (1)

Umut K
Umut K

Reputation: 1388

try this : 'Table 2' is your Calendar Table which is also a slicer on the visual.

Make sure that your Calendar Table's Date has a relation with the Hired Date and also the relation between the Department Table

relations

Count of Emp =
VAR _latest =
    MAX ( 'Table 2'[Date] )
VAR _from =
    MIN ( 'Table 2'[Date] )
VAR _dept =
    SELECTEDVALUE ( Department[Department] )
RETURN
    CALCULATE (
        COUNTX ( 'Table', 'Table'[ID ] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Terminated Date ] >= _from
                && 'Table'[Hired Date ] <= _from
                && 'Table'[Terminated (Y/N)] = "Y"
                && RELATED ( Department[Department] ) = _dept
        )
    )
        + CALCULATE (
            COUNTX ( 'Table', 'Table'[ID ] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Terminated (Y/N)] = "N"
                    && 'Table'[Hired Date ] <= _from
                    && RELATED ( Department[Department] ) = _dept
            )
        )

Result

Upvotes: 1

Related Questions