Forum Discussion

Dave1mo1's avatar
Dave1mo1
Helper III
2 years ago
Solved

Average Employee Tenure Over Time - Line Chart

Hi, all.

 

I'm working on a measure that I can't quite figure out, and I think it may be a relationship issue with my date table. 

 

The goal: Produce a line chart with the average employee tenure on any specific date over the life of my data set.

 

What I have (2 Measures): 

 

1. 
Tenure Days as of Date Chosen =
VAR DateChoose =
    MAX ( 'FY Date Table'[Date] )
RETURN
    IF (
        MAX (Employee[Date of Hire] ) > DateChoose,
        "#NUM!",
        DATEDIFF (
            MAX ( Employee[Date of Hire] ),
            MIN ( COALESCE ( MAX ( Employee[Actual Termination Date] ), TODAY () ), DateChoose ),
            DAY
        )
    )
 
2.
Current Employees = calculate(
    countx(
        filter(
            'Employee',Employee[Date of Hire] <=max('FY Date Table'[Date])
            && (isblank(Employee[Actual Termination Date])
            || Employee[Actual Termination Date] > max('FY Date Table'[Date]))), Employee[Employee Number]), crossfilter('FY Date Table'[Date], Employee[Date of Hire], None))
 
 
I have a date table with an active relationship to my "Date of Hire" field and an inactive relationship to my "Actual Termination Date" field.
 
How do I get where I want to go? I'm a relative novice at DAX and feel in over my head.
Need Help
  • gmsamborn's avatar
    gmsamborn
    2 years ago

    Hi  Dave1mo1 

     

    Would something like this help?

     

    Avg Tenure = 
VAR _Curr = SELECTEDVALUE( 'Date'[Date] )
VAR _Table =
	ADDCOLUMNS(
		FILTER(
			SUMMARIZE(
				ALL( 'Employee' ),
				'Employee'[ID],
				'Employee'[Date of Hire],
				'Employee'[Actual Termination Date]
			),
			[Date of Hire] <= _Curr
				&& COALESCE( [Actual Termination Date], _Curr ) >= _Curr
		),
		"__Days",
			DATEDIFF( [Date of Hire], _Curr, DAY ) + 1
	)
VAR _Avg =
	AVERAGEX(
		_Table,
		[__Days]
	)
RETURN
	_Avg

     

     

    Let me know if you have any questions.

     

    Average Employee Tenure Over Time - Line-Chart.pbix

     

5 Replies