cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculate average tenure based on data in another field

Hi all,

I'm looking for help on a piece of DAX that has had me stumped for a few days now, although it's more than likely very simple. I have a table in my data model like the image below that contains fields EmpNo, LastName, FirstName, StartDate, EndDate, and TenureYears. The TenureYears column is a calculated column based on the age of the StartDate.

I would like to average the TenureYears of only the rows with a blank end date. For example, the correct answer, in this case, would be 5.5. Not 4.2 which is an average of the whole column. Similarly, I would like to create a second measure to calculate the average TenureYears of only the rows with an end date. I have tried a number of expressions but so far haven't managed to find the correct one.

Can anyone point me in the right direction?

A link to the example data set is attached.

https://1drv.ms/x/s!AooV7yMIZkprj2foRPCEW_E7CVes?e=8fzjhe

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

## Re: Calculate average tenure based on data in another field

@Sgeater , try

blank end date

calculate(average(Table[tenure year], isblank(Table[end Date]))

non blank end date
calculate(average(Table[tenure year], not(isblank(Table[end Date]))

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

2 REPLIES 2
Highlighted
Super User IV

## Re: Calculate average tenure based on data in another field

@Sgeater , try

blank end date

calculate(average(Table[tenure year], isblank(Table[end Date]))

non blank end date
calculate(average(Table[tenure year], not(isblank(Table[end Date]))

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

## Re: Calculate average tenure based on data in another field

@amitchandak Thank you. Worked great... I knew it was a simple solution!

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors