Creating a chart for rolling figure in date format
Hi all, I have built a employee turnover metric that looks at average headcount and total leavers in a 12 month timeframe. To get the % figures I have built a Date Table that when I slide the start and end period of each month it returns me the complete breakdown of the data I need. Rolling Turnover % (Total) = DIVIDE([Rolling Leavers (Total)],[Avg Monthly Headcount (12M)],0) However, when reporting on trends it makes it difficult as I have to move the date table periods each time to the 12 month outlook I need. For example for December 2024 I need to put 01/01/2024 - 31/12/2024, for January 2025 it would be 01/02/2024 - 31/01/2025. The Date Table is linked to my main staff file, which has each month of the period 01/2024, 02/2024 etc, and then linked also to my exits table that has the last day per individual. This gives me exactly what I need but it would be very helpful to try and investigate if there is a way to add to this to be able to build some charts over time to better understand trends. Appreciate I may be lacking a lot of info still required, will try my best to share anything that is not clear. Kind regards,Determine Average Employee Count for each year & Count Total Terminations, to Derive Turnover %
Hi Wonderful People, I need help creating a rolling Turnover % Calculation for our HR Team. For Each Year and Month, The average number of Active Staff we had for each year, And the Total Terminations for Each Year, So I can use these two variables, to calculate the Turnover %. This way, i can use a line chart with Year-Month & the Turnover % and Drill down by Division. I have Three Tables modelled below. 1.) Employee Data Table = Employee ID | Hire Date | Termination Date | Division. (this table has all employees for the past 8 Years) 2.) Date Table 1 = Connected to Hire Date 3.) Date Table 2 = Connected to Termination Date. My current Rolling Head Count Calculation= This calculation appears to be correct. " CALCULATE(DISTINCTCOUNT(ADP_CurrentActive[EMP_CODE]), FILTER(ADP_CurrentActive,ADP_CurrentActive[Termination Reason]<>"Employee did not commence"), FILTER( VALUES( 'ADP_CurrentActive'[Hire Date]), ADP_CurrentActive[Hire Date] <= MAX( DATETABLE_DIM[Date] ) ), FILTER( VALUES( ADP_CurrentActive[TERM_DATE]), OR( 'ADP_CurrentActive'[TERM_DATE] >= MIN( DATETABLE_DIM[Date]), ISBLANK( 'ADP_CurrentActive'[TERM_DATE])))) " If the Employee is not Terminated- I fill in the Blank Term Date with a Static Date of 01/01/2050. All Terminations Calculation = This Calculation is correct when i validate. " CALCULATE(DISTINCTCOUNT(ADP_CurrentActive[EMP_CODE]), FILTER(ADP_CurrentActive,ADP_CurrentActive[TERM_DATE]<>01/01/2050), USERELATIONSHIP(ADP_CurrentActive[TERM_DATE],TERMDATETABLE_DIM[Date])) What do you think would be the best approach to this. I think it would be best to create a Custom Table, for each Year/Month, with Total Staff at End of Year, and Total Terminations at End of Year, then add a calculated column from there to determine the Turnover %. But i do not know how to do this 😞 Thank you
