Average Employee Tenure Over Time - Line Chart

[Dave1mo1]

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.

[gmsamborn · Accepted solution]

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

[Dave1mo1]

Sorry it took so long to get back to you - this is perfect, thank you!

Is there any way to tweak the measure so that it responds to filters on my filter panel, however? It's also possible I'm doing something wrong and it's not an issue with the measure, of course.

[gmsamborn]

Hi @Dave1mo1 

I think I know which changes to make but to be sure, I'll need a pbix with dummy data.  (See my first post.)

Hopefully it won't take many changes.

[Dave1mo1]

I've tried a couple times to include the link but failed. Can you see if this link works?

[gmsamborn]

Hi @Dave1mo1 

Can you provide the following?

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

A .pbix file with sample data would be best.