pivotronics

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