Power BI DAX and Visualizations: Active employees across time (as x-axis) by Tenure group or age group

Business Case raised by my youtube channel viewer on the 21st July 2023.

In my employee tenure data, it has employee birthday, hire date and leaving date, and race,

gender, etc. Do you have any idea how to create a dynamic measure of tenure and/or age,

as well as a "tenure group" and "age group" that can change with the calendar date? I would like

to show in a line chart the changing number of active employees across time (as x-axis).

Furthermore, I would like to breakdown the number of employees by their age group or tenure group

(using age group as a legend). The age or tenure group will be dynamically change according to

the dates in the X-axis. Any suggestions are appreciated.

My suggestions:

Assumptions on what we have for the case:

                        - an employee table with coloumns: table update(or registered) date,

                        employeeID, birthdate, hiredate and leavingdate.

                        - an calendar Date table with date, year, querter number or name, month number or name, and

                            period number ( DAX for the Period number as :

                            periodnumber = VALUE(CONCATENATE(YEAR([Date]), RIGHT("0" & MONTH([Date]), 2))) )

                        - Calendar table column date has one to many relation link with UpdateDate column of

                        employee table.

My quick solution:

                     

                        Step1 Calcaulte the measures for counting ActiveEmployee, my DAX formula as

                                

               EmpCount = CALCULATE(

                                    COUNT([EmplID]),

                                            FILTER(ALL('Date'[PeriodNumber]),

                                               'Date'[PeriodNumber] = MAX('Date'[PeriodNumber])

                                           )

                                        )                

               ActiveEmployees = CALCULATE([EmpCount], FILTER(Employee, ISBLANK(Employee[LeavingDate])))

              

             OR just one DAX formula as:

                    
             ActiveEmployees =

                          CALCULATE(

                           COUNT('Employee'[EmplID]),

                           FILTER('Employee', ISBLANK('Employee'[LeavingDate])),

                           FILTER(ALL('Date'[PeriodNumber]),

                                    'Date'[PeriodNumber] = MAX('Date'[PeriodNumber]))

                                 )

 

          

                        Step 2: Create column measure in employee table for DynamicTenureDays as:

                            
                        DynamicTenureDays =

                IF(

                    ISBLANK([LeavingDate]),

                    IF([UpdateDate]<=[HireDate],[HireDate]-[UpdateDate],[UpdateDate]-[HireDate]),

                    [LeavingDate] - [HireDate]

                    )

                    Step 3 : Create column measure in employee table for DynamicTenureYear as:

                        

                          DynamicTenureYears = DIVIDE([DynamicTenureDays], 365.25)  

                    

                    Step 4: Create column measure in employee table for DynamicTenureGroup, for eample as:

                        DynamicTenureGroup =

                SWITCH(

                       TRUE(),

                       [DynamicTenureYears] <= 1, "0-1 years",

                       [DynamicTenureYears] <= 3, "1-3 years",

                       [DynamicTenureYears] <= 5, "3-5 years",

                       [DynamicTenureYears] <= 10, "5-10 years",

                       [DynamicTenureYears] <= 20, "10-20 years",

                       "20+ years"

                       )

            Step 5. Create Column measure in employee table for DynamicAges, for exmaple as:

                            
                DynamicAges =

                    VAR MMDD_Num_UpdateDate = VALUE(FORMAT([UpdateDate],"MMDD"))

                    VAR MMDD_Num_BD =

                        IF(ISBLANK([EmployeeBirthDate]),

                        "Blank",

                        [EmployeeBirthDate]

                        )

                    Return

                        if(

                           MMDD_Num_BD="Blank",

                           0,

                           if(

                               MMDD_Num_UpdateDate>= VALUE(FORMAT(MMDD_Num_BD,"MMDD")),

                               DATEDIFF([EmployeeBirthDate],[date],YEAR),

                               DATEDIFF([EmployeeBirthDate],[date],YEAR)-1

                                )

                            )

             Step 6. Create column measure in employee table for DynamicAgeGroup as:

                            
                    DynamicAgeGroup =

                        SWITCH(

                                TRUE(),

                                [DynamicAges] <= 30, "20-30 years",

                                [DynamicAges] <= 40, "30-40 years",ate

                                [DynamicAges] <= 50, "40-50 years",

                                "50+ years"

                                )

            Step 7. Create line charts to show the changing number of active employees across time (as x-axis) by

                                 by the breakdown of Tenure group or age group:

                                

                                Line chart creations

                                - place measure activeEmployee in Y-axis

                                - place the DynamicTenureGroup or DynamicAgeGroup in legend

                                - place year or created hierarchy (year/quarter/periodnumber) in X-axis.

                                

                            Slicer creation:

                                - place a created hierarchy (year/quarter/periodnumber) in visual field

                                - format the visual style as vertical list.

                       The solution visuals looks like:

** Comments from Viewer:

Thanks, Mike. I tried to replicate the analysis you did. But I cannot create the dynamic column measure Dynamic Tenure Years. The error message is that "the value for DyanmicTenureDays cannot be determined. ... no current row for this column". Can you figure our what is the issue?

** Reply from Me: 

Hi Lijun, in your employee table do you have update date/time column  which we call it as time stamper for your employee table? which tells when did your guy or system update the whole employee  table. usually in HR system the update frequency is once a month . with this time stamper you can have hostroical view to now of your emplyees hire status regrading the change trend of tenure group and age group. if you do not have the time stamper in your employee table. one my suggested workaround is just to create additional column measure in employee table  as "UpdateDate =Today()" . then follow my blog to calcaulte tenure days , then tenure years. with this solution you alway get current view  (also call a basic dynamic view at today) about your active employee tenure group status and age group(but no histroical info). if you want apply line chart for the workaround solution. you need to place activeEemplyee measure in Y axis and age group or tenure group measure in X axis , Calendar date slicer in the report page should cover today's date. please try this workaround first and let me know your feedback. I shall find time to add the scenario (missing time stamper in employee table) in my blog. regards Mike.

** Comments from Viewer:

Thanks Mike. I tried to replicate what you did. I can create the column measure "dynamic tenure days", but when I came to create the column DynamicTenureYears = DIVIDE([DynamicTenureDays], 365.25) in the Employee table, the column measure cannot be created. The error message is that "The value for "DynamicTenureDays" cannot be determined. Either the column does not exit, or there is no current row for this column". The column "dyanmictenuredays" is already created and has numeric values in the employee table. Can you figure out what is the problem?

** Reply from Me: 

Please check the speelings for each your created column measure name, start from updatedDate, DynamicTenureDays, and ensure that they are correctly speelings in your all related clacaultions. as my feedback to your raised repliacted issues in my youtube channge. it should work as I proposed. please refer to ScreenCopies in followings: