Calculate Age in Years and Months in Power BI

If you have been working with Excel you can use the DATEDIF function to find the age in years and months. But finding the age can be tricky in Power BI. In this post I am going to show you 2 ways (using DAX and M) to find age in years and months in Power BI

Let’s start with this simple data

#1 Find Age Using Power Query

For the given date of birth, I’d like to find the completed years and months.

Unfortunately there isn’t a DATEDIF function available in Power Query. So we’ll write our own custom function. Follow the steps

Step 1 – Create a Blank Query

1. In Excel go to Data Tab
2. Under the Get Data drop down >> From Other Sources
3. Select a Blank Query

In Power BI the Blank Query option is under the Get Data drop down in the Home Tab

 

Step 2 – Create the Custom Function for Calculating Age

1. In the Advanced Editor of the blank query paste the code given below
2. Rename the Query (which is now a function) to something more sensible.. let’s say FindAge
3. Alert – The function name has to be written the exact way while using it anywhere in the query

M Code

(YourDate as date) =>
let
    DateCheck = 
    if 
        Number.From(YourDate) > Number.From(DateTime.LocalNow()) 
    then 
        null 
    else 
        YourDate,
    
    MyYears = 
    Date.Year(DateTime.LocalNow()) - Date.Year(DateCheck) + 
    (
    if 
        Date.Month(DateCheck) > Date.Month(DateTime.LocalNow())
    then 
        - 1
    else
        if 
            Date.Month(DateCheck) = Date.Month(DateTime.LocalNow()) and 
            Date.Day(DateCheck) < Date.Day(DateTime.LocalNow())and
            Date.Year(DateCheck) > Date.Year(DateTime.LocalNow()) 
        then
            -1 
        else 
            if
                Date.Month(DateCheck) = Date.Month(DateTime.LocalNow()) and 
                Date.Day(DateCheck) > Date.Day(DateTime.LocalNow())and
                Date.Year(DateCheck) < Date.Year(DateTime.LocalNow()) 
            then
                -1
            else
                0
    ),
    
    BalanceMonthsCalc = 
    (Date.Year(DateTime.LocalNow()) * 12 + Date.Month(DateTime.LocalNow())) - 
    (Date.Year( Date.AddMonths(YourDate,MyYears*12)) * 12 + Date.Month(Date.AddMonths(YourDate,MyYears*12))),
    
    BalanceMonths = 
    if 
        Number.From(Date.AddMonths(YourDate,(MyYears * 12) + BalanceMonthsCalc)) > Number.From(DateTime.LocalNow())
    then 
        BalanceMonthsCalc - 1 
    else 
    BalanceMonthsCalc,
    
    Concat = 
    Text.From(MyYears) & ' Yrs ' & 
    Text.From(BalanceMonths) & ' Months'
in
    Concat

 

The above code will land in the advanced editor of the blank query created

 

Step 3 – Now let’s load the data and call this function.

1. In case you have forgotten, I am talking about the few names and their date of birth 😀
2. Once you have loaded the data, ensure that your column for dates (Dob in our case) needs to be a date datatype
3. Create a Custom Column and call the custom function like this =FindAge([Dob])
4. And Boom, you are done!

 

#2 Find Age Using DAX

If you are more of DAX lover (like me), I got you covered. I have the same data in Power BI

The Only Step – Create a New Column and write following DAX code

Age =
VAR YourDate = Data[DOB]
VAR Check =
    NOT ( YourDate > TODAY () )
VAR TotalMonths =
    DATEDIFF ( Data[DOB], TODAY (), MONTH )
VAR MyYears =
    INT ( ( ( DAY ( YourDate ) > DAY ( TODAY () ) ) * -1 + TotalMonths ) / 12 )
VAR MyMonths =
    (
        (
            (
                ( DAY ( YourDate ) > DAY ( TODAY () ) ) * -1 + TotalMonths
            ) / 12
        )
            - INT ( ( ( DAY ( YourDate ) > DAY ( TODAY () ) ) * -1 + TotalMonths ) / 12 )
    ) * 12
VAR BalMonthsCheck =
    ROUND ( MyMonths, 0 )
RETURN
    IF ( Check, MyYears & ' Yrs ' & BalMonthsCheck & ' Months' )

Note a few things

1. The highlighted part of the code will be your date
2. I am asking you to create a column not a measure

And we are done! Here are the results

 

Personally, I found Power Query solution to be much more elegant. Creating a custom function makes it reusable.

What about you? Which one did you prefer? If you have your own method or have a way to make my code more efficient, do drop in a comment 🙂

 

Some More Power BI / DAX Tricks

1. How to do a COUNTIF in Power BI using DAX
2. Related Function not working & how to fix it
3. Combine Data from Multiple Sheets into a Single Sheet
4. DAX Keyboard ShortCuts
5. An Article from Marco Russo on Calculating Age