Average Tenure

You're welcome 🙂

I think the above measure is doing what you want but let me check it with you. Let me know.

I have attached a sample PBIX.

Just to explain the measure:

• It iterates row by row over the table (named YourTable as a placeholder)
• For the purpose of the measure, blank Leaving Dates are replaced with TODAY() using the COALESCE function, then the Hiring Date is subtracted from the Leaving Date.
• So the blank Leaving Dates remain blank in the table, and are just overridden for the measure calculation itself.
• One thing to note is that TODAY() is determined when the measure is calculated in the report, but query results can be cached, so that if you left a report open from one day to the next, you might get results that are based on a past value of TODAY().

Here are some sample outputs using a subset of your posted data plus some additional rows added by me:

In case you were considering adding a column that replaces blank Leaving Dates with TODAY(), you would just need to be aware that this column would generally only be updated when refresh occurs.