How to calculate duration of years in power query

Question

I have to calculate duration between current year and the year of hire of employees.

I have column set up for year of hire

Currently I used formula in custom column

2023-[year of hire]

how can I write a formula where I don’t have to update current year?

please help

Answer 1

Hi. Thank you for your question and reaching out. I’d be more than happy to help you with your query.

You can calculate the duration of years in Power Query by subtracting the start date from the end date and then dividing the result by the number of days in a year. Here's an example in Power Query M language:


let
    startDate = #date(2019,1,1),
    endDate = #date(2023,12,31),
    durationInDays = Duration.Days(endDate - startDate),
    durationInYears = durationInDays / 365
in
    durationInYears



This will give you the duration in decimal years. If you want the duration in whole years, you can round the result down using the RoundDown function. For example:


let
    startDate = #date(2019,1,1),
    endDate = #date(2023,12,31),
    durationInDays = Duration.Days(endDate - startDate),
    durationInYears = durationInDays / 365,
    durationInWholeYears = RoundDown(durationInYears)
in
    durationInWholeYears



This will return the number of full years between the start and end dates.



If the reply was helpful, please don’t forget to upvote or accept as answer, thank you.

Answer 2

Hi @Anitha

I suggest you post this issue to Power BI forum:

https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---