Hi,
I have a dataset which is trended and has employee information from Jan 2021 upto Jan 2023. I have calculated an attrition rate which is total leavers in 12 month period/average headcount in 12 month period. So in my case - Terminations from Feb 2022-Jan 2023/(headcount Feb 2022 + headcount Jan2023/2), Now this number is correct however I would like to plot this in a graph showing month by month how the attrition rate changed. The attrition rate month to month should still show a 12 month attrition rate not monthly attrition. How can i do that? I have tried calculating measures-
average rolling 12 month headount =
VAR Latestdate = LASTDATE('Trended'[Record Date Start of Month])
VAR StartDate = DATEADD(Latestdate, -11, MONTH)
VAR StartHeadcount = CALCULATE([Headcount], 'Trended'[Record Date Start of Month] = StartDate)
VAR EndHeadcount = CALCULATE([Headcount], 'Trended'[Record Date Start of Month] = Latestdate)
RETURN
DIVIDE( StartHeadcount + EndHeadcount, 2)
this shows average correctly but does not show correct values when plotted in a line graph month by month.
Would be grateful if i could get some help with this? 🙂
