topic Re: Average Tenure in DAX Commands and Tips

Average Tenure

Vinay06 — Sun, 14 Mar 2021 12:09:54 GMT

Hi, I want to calculate Average tenure using 'Hiring Date' and 'Leaving date'.but in 'Leaving date' column there are some empty rows, i want replace empty rows with current date. so please help me in creating a dax code.

Re: Average Tenure

OwenAuger — Sun, 14 Mar 2021 13:09:48 GMT

Hi Vinay06

If you want to leave the blank Leaving Dates as they are, and in those cases treat "today" as the Leaving Date (when the measure is evaluated), you can write a measure like this:

Average Tenure (Days) = VAR TodayDate = TODAY () RETURN AVERAGEX ( YourTable, COALESCE ( YourTable[Leaving Date], TodayDate ) -- Blanks changed to TODAY() - YourTable[Hiring Date] )

The above measure calcualtes tenure in days by subtracting dates. You can instead use DATEDIFF if you need something different.

Regards,

Owen

Re: Average Tenure

Vinay06 — Sun, 14 Mar 2021 18:27:13 GMT

Thanks for spending your time for me. Actually i want to include those blank leaving dates but instead of blank value i want to replace them with today date,and can you provide dax code for calculating averege tenure as well.
1) calculate average tenure using Hiring date and Leaving date

2) Replace blank values in Leaving date with today date.

please provide me with a complete dax code for above mentioned points.

Re: Average Tenure

OwenAuger — Sun, 14 Mar 2021 19:53:10 GMT

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.

Re: Average Tenure

Vinay06 — Mon, 15 Mar 2021 04:54:30 GMT

It's working, Thank you so much.