Dismiss Notice

Register now to be one of the first members of this SharePoint Community! Click here it just takes seconds!

Dismiss Notice
Welcome Guest from Country Flag

Calculating the difference between two dates in YEARS, MONTHS, and DAYS in Power BI or Excel

Discussion in 'Official Microsoft News' started by Samuel Lester - MSFT, Apr 6, 2017.

Thread Status:
Not open for further replies.
  1. Samuel Lester - MSFT

    Samuel Lester - MSFT Guest

    Blog Posts:
    0
    How do I calculate an employee’s tenure given their start date?

    How can I determine a person’s age given their birth date?



    In a customer conversation this week, I was asked to help solve this question in Power BI. Interestingly, I was intrigued by this topic almost 5 years ago when I wrote a blog entry to solve this in TSQL, but now it is time to solve it in DAX for use in Excel or Power BI. The challenge with this question is that it sounds so simple, yet turns out to be a bit tricky. There are likely several other creative ways to solve this. If you have found another way, please share it in the comments below.

    Let’s start with a list of employees and their start dates:

    [​IMG]

    Now create a measure to represent today’s date (or whatever date you want to use as the end date for the calculation).

    TodaysDate = FORMAT(TODAY(),”mm/dd/yyyy”)

    I am using YEARFRAC as the basis of the year/month/day calculations. YEARFRAC returns the number of years in a fractional format between two dates. From this value, we can use additional math functions, such as FLOOR() and MOD() to break out the individual year/month/day components.

    Create the following columns in Power BI (or formulas in Excel):

    Years = FLOOR(YEARFRAC(Source[StartDate],[TodaysDate]),1)

    Months = FLOOR(MOD(YEARFRAC(Source[StartDate],[TodaysDate]),1) * 12.0,1)

    Days = SWITCH(DAY(Source[StartDate]) > DAY([TodaysDate]),
    TRUE(), (DAY(EOMONTH([TodaysDate],-1)) – DAY(Source[StartDate])) + (DAY([TodaysDate])),
    FALSE(), (DAY([TodaysDate])-DAY(Source[StartDate])))


    Using these calculations, we can display the date difference in years, months, and days:

    [​IMG]

    The sample .pbix file can be downloaded here to explore on your own.

    Thanks,
    Sam Lester (MSFT)

    Continue reading...
     
Thread Status:
Not open for further replies.

Share This Page

LiveZilla Live Chat Software