Forum Discussion

Vinay06's avatar
Vinay06
Frequent Visitor
5 years ago
Solved

Average Tenure

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...
Tips and Tricks
  • OwenAuger's avatar
    OwenAuger
    5 years ago

    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.

     

     

OwenAuger's avatar
OwenAuger
Super User
5 years ago

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

Vinay06's avatar
Vinay06
Frequent Visitor
5 years ago

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.

 

  • OwenAuger's avatar
    OwenAuger
    Super User
    5 years ago

    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.