Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Sgeater
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. 

 

Sgeater_0-1592924712217.png

 

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
amitchandak
Super User
Super User

@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]))

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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]))

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.