16-Jul turns out to be the birthday of Microsoft Power BI. Here I post a formula in M Script to calculate the age (i.e., duration or difference between two dates) in years, months and days:
let Source= [ PowerBIBDay = #date(2014, 7, 16), Today = Date.From(DateTime.LocalNow()), BirthdayFactor = if Date.DayOfYear(PowerBIBDay)<Date.DayOfYear(Today) then 0 else 1, DayFactor = if Date.Day(Today)<Date.Day(PowerBIBDay) then -1 else 0, Years = Date.Year(Today)- Date.Year(PowerBIBDay) - BirthdayFactor, Months = Date.Month(Today)-Date.Month(PowerBIBDay) + 12*BirthdayFactor + DayFactor, Days = Number.From(Today)-Number.From(#date(Date.Year(Today), Date.Month(Today) + DayFactor, if Date.Day(PowerBIBDay) > Date.Day(Date.EndOfMonth(#date(Date.Year(Today), Date.Month(Today) + DayFactor, 1))) then Date.Day(Date.EndOfMonth(#date(Date.Year(Today), Date.Month(Today) + DayFactor, 1))) else Date.Day(PowerBIBDay))), YearsText = if Years > 0 then Text.From(Years) & " years " else "", MonthsText = if Months > 0 then Text.From(Months) & " Months " else "", DaysText = if Days > 0 then Text.From(Days) & " and Days " else "", PBISays = if PowerBIBDay = Today then "I am just born today " else "I am now on " & Text.Combine({YearsText, MonthsText, DaysText}) ] in Source[PBISays]
As on today, the output of the above formula is: I am now on 5 years 1 and Days.
Happy Learning..!!