Forum Discussion
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.
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.
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.