Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
N5
Regular Visitor

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 1
johnt75
Super User
Super User

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

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.