Forum Discussion

N5's avatar
N5
Regular Visitor
3 years ago

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

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Try

    Tenure =
VAR EndingDate =
    TODAY ()
VAR Mo =
    MOD (
        DATEDIFF (
            'Active Headcount'[Continuous Service Sysco LABS (Internship/Contract/Permanent)],
            EndingDate,
            MONTH
        ),
        12
    )
VAR Yr =
    QUOTIENT (
        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