Group Active Employees based on tenure

[V_20]

Hello Community,

I have the below requirement which i am unable to write a measure. Let me try to explain my data. I have a Fact table as shown below

Table has employee log and tnure at the time of log and their termination date 

I also have a Disconnected Date table. 

Employee id	Log Date	Tenure Months	Termination Date
A	03-04-2024	1
A	04-04-2024	1
A	07-04-2024	1	07-04-2024
A	01-04-2024	1
B	08-04-2024	1
B	10-04-2024	1
B	16-04-2024	2
B	02-04-2024	1	19-04-2024
C	15-04-2024	3
C	08-04-2024	3
C	13-04-2024	3
C	10-04-2024	2
C	02-04-2024	1
C	13-04-2024	2	16-04-2024
D	13-04-2024	2
D	14-04-2024	2
D	09-04-2024	2
D	11-04-2024	2
D	08-04-2024	1
D	13-04-2024	2

Now I have a slicer with the Date table date column as "As of Date"

Output as of date 08-04-2024 is below as shown

As of 08-04-2024 what is the latest record for each employee and who ever is active (termination date blank or more than as of date selected) and their tenure of months on that log date ( ex: A is not active as per 08-04-2024, B tenure is 1 month as of 08-04-2024) 

Output Required: 

Tenure At months	Count of Employees
1	2
2	0
3	1

Hope i made it clear and expecting if any one could help me with measure in calculating this. 

Thanks in advane

[v-shex-msft]

Hi @V_20 ,

Did @Ashish_Mathur 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If that also doesn't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

Cannot understand the expected result.  How did you arive at 2,0,1?  Which are those employees?

[V_20]

Hi Ashish,

the 2,0,1 are the count of employees as of date with tenure months 1,2,3 respectively. 

A got terminated on 7th so as of 8 he is not active employee. He is not conisdered. 

B has tenure of 1 month as of 8th  

C has trnure of 3 months as of 8th

D has tenure of 1month as of 8th date. 

hence the table shows the result of 2 employees with 1 month tenure. 1 employee with 3 month tenure

if 8th date log is not there for a employee, we need to get the max date which is less than as of date to get the latest log of that employee. 

hope this explains. Kindly let me know if you need further explanation.  

thanks for your reply. 

[Ashish_Mathur · Accepted solution]

PBI file attached.

Hope this helps.