HR - Showing Inventory Movements & analysing Total Running Tenure over time

Guides • Data Analytics • 15/09/2020

HR - Showing Inventory Movements & analysing Total Running Tenure over time

This sample takes the cross joined data from the end of this ETL article: HR - Inventory Movements & Tenure, (which explains how to produce Employee Inventory Table Dataset), to show, from this data:

Employee Movements

(i) Employee movements: (External and Internal) across a company - this can be visualised dynamically, across different dimensions, such as: Job Grade, Country, Office, and Department - by both Month, and by Quarter.

Employee Tenure

Also shown are:

(ii) Total Running Tenure (in years):, for each Employee, since starting with the company, and

(iii) Total Running Tenure (in Months):, for each Employee, since starting from a particular Job Grade.

A Tenure measure, by Job Grade, can make it easier to see what each employee’s progression is within the company, and how their ‘Progression Velocity’ compares against other groups of Employees. This may help to identify outliers, for further investigation, but may also help with the further development of existing HR Reward and Remuneration Policies.

Model Extensions

Possible extensions to this model, could include:

Adding ‘Freelance or Permanent’ as an extra field to distinguish between these staff types
Separating the calculations for Freelancers and Permanent Staff so that the Running Tenure calculations only consider the time when a person was a Freelancer, or only when they were Permanent Staff, or both.
Freelance records could be automatically linked and created from their agency Timesheet submissions.
Providing further logic around the way Internal Movements are defined, depending on the filters selected on the page. Refer here for more information on Internal Leaves or Internal Joins

Dashboard - Snippet

Click to view large-sized image

Click to view large-sized image

Click to view large-sized image

Important:
In this report, in order to show the correct results, most tables and charts have additional Page filters applied to them too. Click the red filter bar on the side of each page, to see which filters have been applied to each table and chart.

Data Model - Snippet

Click to view large-sized image

Measures

Count User ID

DAX

Count User ID = 
COUNTA ( 'Cross Joined Staff Table'[User ID] )

Head Count at End of Month

DAX

Head Count at End of Month = CALCULATE (
    COUNTA ( 'Cross Joined Staff Table'[User ID] ),
    'Cross Joined Staff Table'[Part of HeadCount at End of Month] = "Y"
)

Head Count at End of Quarter

DAX

Head Count at End of Quarter = 
CALCULATE (
    [Head Count at End of Month],
    FILTER (
        'Cross Joined Staff Table',
        'Cross Joined Staff Table'[Start of Month]
            = MAX ( 'Cross Joined Staff Table'[Start of Month] )
    )
)

Head Count at Start of Month

DAX

Head Count at Start of Month = CALCULATE (
    COUNTA ( 'Cross Joined Staff Table'[User ID] ),
    'Cross Joined Staff Table'[Part of HeadCount at Start of Month] = "Y"
)

Head Count at Start of Quarter

DAX

Head Count at Start of Quarter = 
CALCULATE (
    [Head Count at Start of Month],
    FILTER (
        'Cross Joined Staff Table',
        'Cross Joined Staff Table'[Start of Month]
            = MIN ( 'Cross Joined Staff Table'[Start of Month] )
    )
)

Job Grade Tenure Mnths Measure

DAX

Job Grade Tenure Mnths Measure = 
VAR JobGradeTenureMths =
    CALCULATE (
        AVERAGE ( 'Cross Joined Staff Table'[Running Tenure Mnths - by Job Grade] ),
        FILTER (
            'Cross Joined Staff Table',
            'Cross Joined Staff Table'[Start Date]
                = MAX ( 'Cross Joined Staff Table'[Start Date] )
        )
    )
RETURN
    IF (
        AND (
            ISFILTERED ( 'Cross Joined Staff Table'[Job Grade] ),
            ISFILTERED ( 'Cross Joined Staff Table'[User ID] )
        ),
        JobGradeTenureMths,
        SUMX (
            SUMMARIZE (
                'Cross Joined Staff Table',
                [User ID],
                [Job Grade],
                [Start of Month],
                "Job Grade Tenure Month",
                    CALCULATE (
                        AVERAGE ( 'Cross Joined Staff Table'[Running Tenure Mnths - by Job Grade] ),
                        FILTER (
                            'Cross Joined Staff Table',
                            'Cross Joined Staff Table'[Start Date]
                                = MAX ( 'Cross Joined Staff Table'[Start Date] )
                        )
                    )
            ),
            [Job Grade Tenure Month]
        )
    )

Job Grade Tenure Mnths Measure (Alternative)

DAX

Job Grade Tenure Mnths Measure (Alternative) =
VAR JobGradeTenureMths =
    CALCULATE (
        SUM ( 'Cross Joined Staff Table'[Days in Service] ),
        ALLEXCEPT (
            'Cross Joined Staff Table',
            'Cross Joined Staff Table'[User ID],
            'Cross Joined Staff Table'[Job Grade],
            'Cross Joined Staff Table'[Start of Month]
        )
    ) * 12 / 365.25
RETURN
    IF (
        AND (
            ISFILTERED ( 'Cross Joined Staff Table'[Job Grade] ),
            ISFILTERED ( 'Cross Joined Staff Table'[User ID] )
        ),
        JobGradeTenureMths,
        SUMX (
            SUMMARIZE (
                'Cross Joined Staff Table',
                [User ID],
                [Job Grade],
                [Start of Month],
                "Job Grade Tenure Month",
                    CALCULATE (
                        SUM ( 'Cross Joined Staff Table'[Days in Service] ),
                        ALLEXCEPT (
                            'Cross Joined Staff Table',
                            'Cross Joined Staff Table'[User ID],
                            'Cross Joined Staff Table'[Job Grade],
                            'Cross Joined Staff Table'[Start of Month]
                        )
                    ) * 12 / 365.25
            ),
            [Job Grade Tenure Month]
        )
    )

Leavers in Month (Both)

DAX

Leavers in Month (Both) = 
CALCULATE (
    COUNTA ( 'Cross Joined Staff Table'[User ID]),
    'Cross Joined Staff Table'[Left in Month ?] = "Y"
)*-1

Leavers in Month (External)

DAX

Leavers in Month (External) = CALCULATE (
    COUNTA ( 'Cross Joined Staff Table'[User ID]),
    'Cross Joined Staff Table'[Left in Month ?] = "Y",
    'Cross Joined Staff Table'[External Leave] = "Y"
)*-1

New Hires in Month (Both)

DAX

New Hires in Month (Both) = CALCULATE (
    COUNTA ( 'Cross Joined Staff Table'[User ID]),
    'Cross Joined Staff Table'[Started in Month ?] = "Y"
)

New Hires in Month (External)

DAX

New Hires in Month (External) = CALCULATE (
    COUNTA ('Cross Joined Staff Table'[User ID]),
    'Cross Joined Staff Table'[Started in Month ?] = "Y",
    'Cross Joined Staff Table'[External Join] = "Y"
)

Running Tenure Years Measure

DAX

Running Tenure Years Measure = 
VAR TenureYrs =
    CALCULATE (
        AVERAGE ( 'Cross Joined Staff Table'[Running Tenure Yrs] ),
        FILTER (
            'Cross Joined Staff Table',
            'Cross Joined Staff Table'[Start Date]
                = MAX ( 'Cross Joined Staff Table'[Start Date] )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Cross Joined Staff Table'[User ID] ),
        TenureYrs,
        AVERAGEX (
            SUMMARIZE (
                'Cross Joined Staff Table',
                [User ID],
                [Start of Month],
                "Tenure Years",
                    CALCULATE (
                        AVERAGE ( 'Cross Joined Staff Table'[Running Tenure Yrs] ),
                        FILTER (
                            'Cross Joined Staff Table',
                            'Cross Joined Staff Table'[Start Date]
                                = MAX ( 'Cross Joined Staff Table'[Start Date] )
                        )
                    )
            ),
            [Tenure Years]
        )
    )

Running Tenure Years Measure (Alternative)

DAX

Running Tenure Years Measure (Alternative) =
VAR TenureYrs =
    CALCULATE (
        SUM ( 'Cross Joined Staff Table'[Days in Service] ),
        ALLEXCEPT (
            'Cross Joined Staff Table',
            'Cross Joined Staff Table'[User ID],
            'Cross Joined Staff Table'[Start of Month]
        )
    ) / 365.25
RETURN
    IF (
        ISFILTERED ( 'Cross Joined Staff Table'[User ID] ),
        TenureYrs,
        AVERAGEX (
            SUMMARIZE (
                'Cross Joined Staff Table',
                [User ID],
                [Start of Month],
                "Tenure Years",
                    CALCULATE (
                        SUM ( 'Cross Joined Staff Table'[Days in Service] ),
                        ALLEXCEPT (
                            'Cross Joined Staff Table',
                            'Cross Joined Staff Table'[User ID],
                            'Cross Joined Staff Table'[Start of Month]
                        )
                    ) / 365.25
            ),
            [Tenure Years]
        )
    )

New Hires in Month (External)

DAX

Today = TODAY()

Tables (DAX Alternative)

Cross Joined Staff Table (DAX)

Note:
Instead of using ETL procedures, with ‘M’ Code to generate the full Inventory table, DAX can be used instead to create this. The code below shows how to cross join the Calendar Table with the initial Inventory Table.

DAX

Cross Joined Staff Table (DAX) = 
CROSSJOIN('Monthly Calendar', Inventory_Table)

Calculated Columns (DAX Alternative)

Note:
This section shows how to add additional calculated columns to the Cross Joined table, using DAX, instead of with ETL procedures, and/or ‘M’ code.

Days in Service

DAX

Days in Service = 
VAR StartDate = [Start Date]
VAR EndDate =
    IF ( ISBLANK ( [End Date] ), TODAY (), [End Date] )
RETURN
    IF (
        MIN ( MIN ( TODAY (), EndDate ), [End of Month] ) < StartDate,
        BLANK (),
        MIN ( MIN ( TODAY (), EndDate ), [End of Month] ) - StartDate + 1
    )

End Date Fill

DAX

End Date Fill = 
IF ( ISBLANK ( [End Date] ), 2958100, [End Date] )

External Join

DAX

External Join = 
IF(([Start Date] - [Previous End Date]) > 30, "Y", "N")

External Leave

DAX

External Leave = 
IF(([Next Start Date] - [End Date Fill]) > 30, "Y", "N")

Internal Join

DAX

Internal Join = 
IF([External Join] = "Y", "N", "Y")

Internal Leave

DAX

Internal Leave = 
IF([External Leave] = "Y", "N", "Y")

Note:
We have implicitly assumed here that anything which is not an External Leave or External Join is an Internal Leave or Internal Join.

You may not always want to assume this - for example, if a new Employee record is created because of a change in their working status from Part to Full, or because of a change in just Job Grade, or Salary - depending on your own business definitions, you might not want to consider these to be even Internal changes.

The example, here, in this DAX article: HR - Inventory, Tenure, & Salary (created after this article was created), adds the extra condition that if the change is not External, then it is only an Internal change, if there has been a change in the Employee’s Country, Office, or Department.

Left in Month ?

DAX

Left in Month ? = 
IF (
    AND ( [End Date Fill] >= [Start of Month], [End Date Fill] <= [End of Month] ),
    "Y",
    "N"
)

Next Start Date

DAX

Next Start Date =
IF (
    ISBLANK (
        CALCULATE (
            MIN ( 'Cross Joined Staff Table (DAX)'[Start Date] ),
            FILTER (
                ALLEXCEPT (
                    'Cross Joined Staff Table (DAX)',
                    'Cross Joined Staff Table (DAX)'[User ID],
                    'Cross Joined Staff Table (DAX)'[Start of Month]
                ),
                'Cross Joined Staff Table (DAX)'[Start Date]
                    > EARLIER ( 'Cross Joined Staff Table (DAX)'[Start Date] )
            )
        )
    ),
    2958465,
    CALCULATE (
        MIN ( 'Cross Joined Staff Table (DAX)'[Start Date] ),
        FILTER (
            ALLEXCEPT (
                'Cross Joined Staff Table (DAX)',
                'Cross Joined Staff Table (DAX)'[User ID],
                'Cross Joined Staff Table (DAX)'[Start of Month]
            ),
            'Cross Joined Staff Table (DAX)'[Start Date]
                > EARLIER ( 'Cross Joined Staff Table (DAX)'[Start Date] )
        )
    )
)

Part of HeadCount at End of Month

DAX

Part of HeadCount at End of Month = 
IF (
    AND ( [Start Date] <= [End of Month], [End Date Fill] > [End of Month] ),
    "Y",
    "N"
)

Part of HeadCount at Start of Month

DAX

Part of HeadCount at Start of Month = 
IF (
    AND ( [Start Date] < [Start of Month], [End Date Fill] >= [Start of Month] ),
    "Y",
    "N"
)

Part of HeadCount Start or End of Month

DAX

Part of HeadCount Start or End of Month = 
IF (
    OR (
        [Part of HeadCount at Start of Month] = "Y",
        [Part of HeadCount at End of Month] = "Y"
    ),
    "Y",
    "N"
)

Previous End Date

DAX

Previous End Date = 
IF (
    ISBLANK (
        CALCULATE (
            MAX ( [End Date Fill] ),
            FILTER (
                ALLEXCEPT (
                    'Cross Joined Staff Table (DAX)',
                    'Cross Joined Staff Table (DAX)'[User ID],
                    'Cross Joined Staff Table (DAX)'[Start of Month]
                ),
                [End Date Fill] < EARLIER ( 'Cross Joined Staff Table (DAX)'[End Date Fill] )
            )
        )
    ),
    0,
    CALCULATE (
        MAX ( [End Date Fill] ),
        FILTER (
            ALLEXCEPT (
                'Cross Joined Staff Table (DAX)',
                'Cross Joined Staff Table (DAX)'[User ID],
                'Cross Joined Staff Table (DAX)'[Start of Month]
            ),
            [End Date Fill] < EARLIER ( 'Cross Joined Staff Table (DAX)'[End Date Fill] )
        )
    )
)

Running Tenure Mths - by Job Grade

DAX

Running Tenure Mths - by Job Grade = 
CALCULATE (
    SUM ( [Days in Service] ),
    ALLEXCEPT (
        'Cross Joined Staff Table (DAX)',
        'Cross Joined Staff Table (DAX)'[Start of Month],
        'Cross Joined Staff Table (DAX)'[User ID],
        'Cross Joined Staff Table (DAX)'[Job Grade]
    )
) / 365.25 * 12

Running Tenure Yrs

DAX

Running Tenure Yrs = 
CALCULATE (
    SUM ( [Days in Service] ),
    ALLEXCEPT (
        'Cross Joined Staff Table (DAX)',
        'Cross Joined Staff Table (DAX)'[Start of Month],
        'Cross Joined Staff Table (DAX)'[User ID]
    )
) / 365.25

Started in Month ?

DAX

Started in Month ? = 
IF (
    AND ( [Start Date] >= [Start of Month], [Start Date] <= [End of Month] ),
    "Y",
    "N"
)

Tenure Bracket

DAX

Tenure Bracket = 
SWITCH (
    TRUE (),
    [Running Tenure Yrs] = 0, "Nil",
    [Running Tenure Yrs] > 0
        && [Running Tenure Yrs] <= 0.5, "0 to 0.5 years",
    [Running Tenure Yrs] > 0.5
        && [Running Tenure Yrs] <= 1, "0.5 to 1 year",
    [Running Tenure Yrs] > 1
        && [Running Tenure Yrs] <= 3, "1 to 3 years",
    [Running Tenure Yrs] > 3
        && [Running Tenure Yrs] <= 5, "3 to 5 years",
    [Running Tenure Yrs] > 5
        && [Running Tenure Yrs] <= 8, "5 to 8 years",
   [Running Tenure Yrs] > 8
        && [Running Tenure Yrs] <= 1000, "More than 8 years",
    "Other"
)

HR - Showing Inventory Movements & analysing Total Running Tenure over time