Forum Discussion

N5's avatar
N5
Regular Visitor
3 years ago
Solved

Tenure Calculation in PowerBI - Incorrect Year calculation

Hi All,
I used the below DAX formula for the tenure calculation in PowerBI. But it gives wrong calculation for the Year.

For example someone who joined in 25 March 2019. Ideally it should be 3 Years and 10 Months. DAX formula gives 04 Years and 10 Months. Can someone help me correcting this formula?

Tenure =

VAR EndingDate =
 TODAY()
 
VAR Yr = DATEDIFF('Active Headcount'[Continuous Service Sysco LABS (Internship/Contract/Permanent)], EndingDate, YEAR)
VAR Mo = MOD(DATEDIFF('Active Headcount'[Continuous Service Sysco LABS (Internship/Contract/Permanent)], EndingDate, MONTH),12)

VAR Tenure =
 IF(
 Yr > 0,
 COMBINEVALUES(" ", Yr,"Years", Mo,"Months"),
 COMBINEVALUES(" ", Mo,"Months")
 )
 
RETURN Tenure
General Comment
Need Help
Show and Tell
Tutorial Requests
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi N5 

    try like:

    Tenure = 
VAR _months = DATEDIFF('Active Headcount'[Continuous Service Sysco LABS (Internship/Contract/Permanent)], TODAY(), MONTH)
VAR _year = TRUNC(DIVIDE(_months, 12))
VAR _month = MOD(_months, 12)
RETURN
IF(_year=0,BLANK(), IF(_year=1, _year&" Year ", _year&" Years "))
&
IF(_month=0, BLANK(), IF(_month=1, _month&" Month", _month&" Months"))

     

3 Replies

  • pratyashasamal's avatar
    pratyashasamal
    Memorable Member
    3 years ago

    Hi N5 ,

    Tenure Months =
    var yr = datediff([start_Date],YEAR(TODAY()), year)
var mn =datediff([start_Date],MONTH(TODAY()), month) - (datediff([start_Date],YEAR(TODAY()), year) *12))

Return yr + " Years, " + mn + " Months"

    Please try this as well
    Thanks ,

    Pratyasha Samal
    Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
    If you found this post helpful, please give Kudos C

     

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi N5 

    try like:

    Tenure = 
VAR _months = DATEDIFF('Active Headcount'[Continuous Service Sysco LABS (Internship/Contract/Permanent)], TODAY(), MONTH)
VAR _year = TRUNC(DIVIDE(_months, 12))
VAR _month = MOD(_months, 12)
RETURN
IF(_year=0,BLANK(), IF(_year=1, _year&" Year ", _year&" Years "))
&
IF(_month=0, BLANK(), IF(_month=1, _month&" Month", _month&" Months"))

     

    • N5's avatar
      N5
      Regular Visitor
      3 years ago

      This worked. Thank you soo much!