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..!!
