Filtering Calculated 'Tenure' Measure Based on Filter selection and then Gouping it

[Anonymous]

Hi Team,

We are trying to  calculate Tenure for the active employees by giving a dynamic date input from the user . Active employees is a  measure named Headcount. The formula we are using  for calculating Tenure is :

Tenure = var _date=SELECTEDVALUE('calendar'[Date])-MAX('HC Snapshot'[orig_hire_dt])

return

IF(_date>0,_date/365.25,BLANK())

But using this condition we are getting tenure for all the employees(active and inactive).

The calendar table used here is not related to any of the base tables and is created on Power BI for selecting the date.

Please let us know the following things:

1)We need to filter Tenure with the active employee measure (Headcout).

2)We also need to calculate average for the Tenure

3)We have to create bins for average Tenure

which is multiple timestamp conditions validated against the date selected on the date slicer.

The formula we are using is

[v-frfei-msft]

Hi @Anonymous ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.

Regards,

Frank

[Anonymous]

Hi Frank

With this data we are applying the following records to get the active records. i.e

 Headcount = IF(SELECTEDVALUE('Calendar'[Date]),

CALCULATE (

[distinct_count],

FILTER (

'Sample_data',

'Sample_data'[working_strt_date_ts] < SELECTEDVALUE ( 'calendar'[Date] )

&& 'Sample_data'[working_end_date_ts] > SELECTEDVALUE ( 'calendar'[Date] )

&& 'Sample_data'[Org_date_strt_ts]< SELECTEDVALUE ( 'calendar'[Date] )

&& 'Sample_data'[Org_date_end_ts]> SELECTEDVALUE ( 'calendar'[Date] )

  ) , 0 )
 
 
  Here 1)[distinct_count] = DISTINCTCOUNT('Sample_data'[employee_id])
             2)Calendar Date = This we have created manually on Power BI to have unique calendar table and this is where the user is giving the date input so as to make the dashboard point in time
  
  
  Our Requirement :
  
  1) We want Point in time Tenure and Point in time avg tenure

                 Tenure Required Formula -  (Calendar_date - Hire_Date)/365.25 --> Here Calendar_date  is selected by  user

                 The Dax created for the same  -  

                       Tenure = var _date=SELECTEDVALUE('calendar'[Date])-MAX('Sample_data'[hire_dt])
                                                    return
                                                    IF(_date>0,_date/365.25,BLANK())

        2) We want to create Bins for the Tenure and show it against the Planning_org in a Heat map

                  For Example - 
      
                    (i)1 to 2 Years   for Tenure less than 1
                    (ii)2 to 3 Years    For Tenure greater than 1 and less than 2  

 Note : We do not have a unique date column in our data hence we have created the Calendar Table on Power BI

Sample_Data

Employee_Id	Planning_Org	Planning_sub_org	Hire_Dt	working_strt_date_ts	working_date_end_ts	Org_date_strt_ts	Org_date_end_ts
101129	Sales	Sales_engineer	07/02/79 0:00	09/25/2017 18:02	12/31/9999 23:59	06/11/2018 18:05	12/31/9999 23:59
101130	Marketing	Marketing_engineer	07/15/85 0:00	06/02/2018 18:02	12/31/9999 23:59	06/11/2018 18:05	12/31/9999 23:59
101130	Marketing	Marketing_CS	07/15/85 0:00	09/25/2017 6:02	06/02/2018 18:02	06/11/2018 18:05	12/31/9999 23:59
101131	PR	PR_engineer	10/05/92 0:00	12/18/2018 6:02	12/31/9999 23:59	12/18/2018 18:04	12/31/9999 23:59
101131	PR	PR_CS	10/05/92 0:00	09/10/2017 6:04	12/18/2018 6:02	09/10/2018 18:04	12/18/2018 18:04
101131	PR	PR_R&D	10/05/92 0:00	06/11/2017 18:05	09/10/2017 6:04	06/11/2018 18:05	09/10/2018 18:04
101132	Operations	Operations_engineer	03/02/81 0:00	12/06/2018 23:59	12/31/9999 23:59	12/31/2018 6:05	12/31/9999 11:59
101132	Operations	Operations_CS	03/02/81 0:00	06/06/2017 18:05	12/06/2018 23:59	06/11/2018 18:05	12/31/2018 6:05