DATEDIFF formula to return Years and Months

[KulCA]

Hello,

I have a formula in Excel where i can calculate the Date Difference in years and months by using the interval "Y" for years and "YM" for months so my date difference would be diplayed as 3 years and 8 months.

In Power BI, I have found the correct formula to calculate the year so it returns 3 years

DATEDIFF([Hire Date],[Report Date],YEAR)

But how can i get the equivalent of the Excel "YM"?  If i do the same formula as above with the MONTH interval, i will get the total number of months from one date to another. However, i only want the number of months that are over the 3 years.  Is there anyway i can do this in BI?

[Greg_Deckler · Accepted solution]

YearMonths = 
VAR Years = DATEDIFF([Hire Date],[Report Date],YEAR) 
VAR Months = DATEDIFF([Hire Date],[Report Date],MONTH) - Years * 12
RETURN CONCATENATE(CONCATENATE(CONCATENATE(Years," Years, "),Months)," Months")

[kyle]

This solution didn't work for me as I was getting negative values like "1 years, -10 months". I figured out this is because DATEDIFF using YEAR is calculated based on the calendar year of the dates, not the actual time elapsed (e.g. 365 days).

As an example, the DATEDIFF using YEAR between 31/12/2016 and 1/1/2017 is "1 year", even though it's only 1 day apart. However the DATEDIFF between 1/1/2017 and 31/12/2017 would be "0 years", despite being 364 days apart.

I modified the formula and this worked for me:

YearMonths = 
VAR Months = DATEDIFF([Hire Date],[Report Date],MONTH)
VAR Years =  ROUNDDOWN(Months/12,0)
RETURN CONCATENATE(CONCATENATE(CONCATENATE(Years," Years, "),Months-(Years*12))," Months")

Technically you'd have the same problem with Months, so if you need it to be more accurate you could add DAY as a variable.

[uj21]

hey kyle, thanks for this formula.  that worked for me, however, i am trying to create a card visualization that shows the average time but i cannot get it to work.  my new dax formula is stored as 'text', but if i swap it to time it errors so i cannot get an actual avg

[Anonymous]

Thanks! It works great! Can you also share code where you added Day? Because CONCATENATE limits to 2 items only. I appreciate your help.

[KulCA]

Hello,

I have a formula in Excel where i can calculate the Date Difference in years and months by using the interval "Y" for years and "YM" for months so my date difference would be diplayed as 3 years and 8 months.

In Power BI, I have found the correct formula to calculate the year so it returns 3 years

DATEDIFF([Hire Date],[Report Date],YEAR)

But how can i get the equivalent of the Excel "YM"?  If i do the same formula as above with the MONTH interval, i will get the total number of months from one date to another. However, i only want the number of months that are over the 3 years.  Is there anyway i can do this in BI?