Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Employee Turnover help

Hello all,

 

I am working on what I think is a simple dashboard, but has proven to be not the case. Or maybe I'm making it complicated. I'm trying to produce a dashboard for Employee Turnover. I have a data model with FullName/HireDate/FireDate/Active etc. So, I'm able to take those Hire/Term Date and display them on a clusterd bar chart with Month/Year on the axis. I would like to take it a few steps further and get the New Hire Count so that I can add that as well. Also would be great to have a running total of active employees so that can be charted as well. Is it possible to add these somehow as measures? If so, can someone advise? 

 

Sample data below:

 

 

 

Thanks in advance! 

Need Help

2 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    8 years ago

    Anonymous

    Supposing that the x-axis is the date column from a calendar table, to get running total of active employees, you can try to create a measure as

     

    running_total_of_active_employees =
COUNTROWS (
    FILTER (
        ALLSELECTED ( yourTable ),
        yourTable[hiredate] <= EOMONTH ( MAX ( CALENDAR[DATE] ), 0 )
            && (
                yourTable[FireDate]  > EOMONTH ( MAX ( CALENDAR[DATE] ), 0 )
                    || ISBLANK ( ( yourTable[FireDate] ) )
            )
    )
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Just to make sure I get this correct, the relationship to my Date table needs to be on the hire date?