Hi All,
We are trying to derive Trailing 12 Month (TTM) Attrition %.
Requirement : As on any particular day, the system should look back 12 month from the previous month.
Example: On Effective Date
From 1 Mar 2024 - 31 Mar 2024:
Closing Headcount = 2500
Employee left in last 12 month (Employee Left based on Termination Date between 1 Feb 2023- 29 Feb 2024.) = 500
Employee Hired in last 12 month (Employee Left based on Hire Date between 1 Feb 2023- 29 Feb 2024.) = 200
Opening Headcount = 2800 (Closing HC - Hired + Terminated)
In this case we have created a Data model level Canculation to identify Date Difference between Effective Date and Termination/Hire Date.
The issue is occuring because we get difference in Day, Month or Year.
We are not able to make this difference show in the required manner.
When we take Month as Granularity, then we are getting the records from 2 Jan 2023 to 31 Jan additionally as the difference is in integer so untill 13 months are completed the system is considering 12 an dreturing records.
The expectation is to create measure to calculate users separated or terminated in last 12 months.
However, when we created a measure at model level using "Month" as granularity the users with termination more than 12 months but less than 13 months are coming in the data.
The result of date difference cannot be used as decimal to apply condition for that.
So as the next option we are looking to derive this calculation based on Days granularity.
But in this case we put condition for 365 Days = 12 months
But in this case the Days Range should end on the last day of previous month.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.