Employee Tenure 12 months before

[ejafarov]

Hello everyone.

I am trying to calculate the tenure of active employees for 12 months before the selected date. COuld you please help me to correct this dax ?

thanks 

the conditions:

1. If the start date is after the date 12 months before the selected date, we consider it as blank because the employee was not part of the organization for the period we are considering.

2. If the start date is before or equals the date 12 months before the selected date and the resignation date is blank (which means the employee is still working), then we calculate the date difference between the start date and the date 12 months before the selected date.

3. If the start date is before or equals the date 12 months before the selected date and the resignation date is also before the date 12 months before the selected date, then we consider it as blank because the employee was not part of the organization for the period we are considering.

4. If the start date is before or equals the date 12 months before the selected date and the resignation date is after the date 12 months before the selected date, then we calculate the date difference between the start date and the date 12 months before the selected date.
   
   
   

   Tenure12MonthsBefore =

   VAR SelectedDate = MAX(Teqvim[Date])

   VAR Date12MonthsBefore = EDATE(SelectedDate, -12) + 1

   
   

   RETURN

   CALCULATE(

       AVERAGEX(

           FILTER(

               ALL(Table1),

               AND(

                   Table1[Start Date] <= Date12MonthsBefore,

                   OR(

                       ISBLANK(Table1[Resignation date]),

                       Table1[Resignation date] > Date12MonthsBefore

                   )

               )

           ),

           IF(

               AND(Table1[Start Date] <= Date12MonthsBefore, ISBLANK(Table1[Resignation date])),

               DATEDIFF(Table1[Start Date], Date12MonthsBefore, MONTH),

               IF(

                   AND(Table1[Start Date] <= Date12MonthsBefore, Table1[Resignation date] > Date12MonthsBefore),

                   DATEDIFF(Table1[Start Date], Date12MonthsBefore, MONTH),

                   BLANK()

               )

           )

       ),

       Teqvim[Date] >= Date12MonthsBefore && Teqvim[Date] <= SelectedDate,

       CROSSFILTER(Teqvim[Date], Table1[Start Date], None)

   )

[technolog]

You've defined two variables at the start: SelectedDate which is the maximum date in the Teqvim table and Date12MonthsBefore which is 12 months before the SelectedDate.

The main logic you're trying to implement is to filter the Table1 based on the conditions you've mentioned and then calculate the average tenure of the employees based on the filtered data.

The logic you've written seems mostly correct, but let's address the conditions you've mentioned:

1. If the start date is after the date 12 months before the selected date, it's considered blank. This condition is not explicitly handled in your DAX, but it's implicitly taken care of because you're filtering out records where the start date is less than or equal to Date12MonthsBefore.
2. If the start date is before or equals the date 12 months before the selected date and the resignation date is blank, then you're calculating the date difference between the start date and Date12MonthsBefore. This is correctly implemented in your DAX.
3. If the start date is before or equals the date 12 months before the selected date and the resignation date is also before the date 12 months before the selected date, then it's considered blank. This condition is not explicitly handled in your DAX. You might need to add this condition to ensure that you're not considering these records.
4. If the start date is before or equals the date 12 months before the selected date and the resignation date is after the date 12 months before the selected date, then you're calculating the date difference between the start date and Date12MonthsBefore. This is correctly implemented in your DAX.

To address the third condition, you can modify the filter condition to:

AND(
Table1[Start Date] <= Date12MonthsBefore,
OR(
ISBLANK(Table1[Resignation date]),
AND(
Table1[Resignation date] > Date12MonthsBefore,
NOT(ISBLANK(Table1[Resignation date]))
)
)
)
This ensures that if the resignation date is before Date12MonthsBefore, those records are not considered.

The rest of your DAX seems fine. You're calculating the average tenure of the employees based on the filtered data and ensuring that the tenure is calculated only for the dates between Date12MonthsBefore and SelectedDate. The CROSSFILTER function at the end ensures that there's no relationship between Teqvim[Date] and Table1[Start Date] while performing the calculations.