Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, because DAX is the preferred language usedin many calculationsin Power BI, many do not realize this feature to Power Query. In this blog article I'll show you how easy it is to calculateAge in Power BI using PowerBI. This methodis extremely efficient when the estimation of your agecan be calculated on a pre-calculated row or row basis.

Calculate Age from a date

Below you can view the DimCustomer table, which makes up the AdventureWorksDW table. It also has a birthdate column. I've removed several of the columns which aren't needed for ease of be read

In order to calculate the actual age of each buyer, the following information is required:

• In Power BI Desktop, Click on Transform Data
• In the Power Query Editor window; begin by selecting the Birthdate column.
• click on the Add Column Tab, then click on"Add Column". Then, click the "From Date & Time" section. Under Date select the age range.

That's it. this calculates the calculate an amount that is the sum of the column for birthdate, Birthdate column, along with the date and time.

However, the date that is displayed within the Age column, doesn't really appear to be an age. It's because it's an actual duration.

Duration

Duration is an exclusive type of data type within Power Query which represents the differences of the two DateTime values. Duration is a combination of four different values:

days.hours.minutes.seconds

and that's how you can interpret the numbers above. However, from the point of view of the user it is not required of them to be able to interpret such information. There are methods to ensure that you are able to obtain every part of the duration. By using the Duration menu, you'll notice that you can take the number of seconds and minutes, hours, the days and years out of it.

For help calculating the age in years by way of example it is simple to click on Total Years:

The duration is calculated in days . Then, it was divided in 365, to calculate the value for the year.

Rounding

Finally, no one declares your age in 53.813698630136983! They are saying 53, which is the number rounded down. It is possible to select Rounding and Round Down in the Transform tab.

This will show you that you're old enough to be

You can then clean the other columns if you'd like (or this could mean that you made use of transformations within the Transform tab, to prevent creating new columns) You can also name this column as Age:

Things to Know

• Refresh The data's age determined using this method will be refreshed when you refresh your database. Each time the system is in a position to compare the birthdate with the date as well as the time in the process of refreshing. It is a method will be an algorithm used to determine the age. If you would like the calculation of age to be done dynamically using DAX here is how I explained what you can make use of.
• The reason behind Power Query: Benefits of using age calculation with Power Query is that the calculation is carried out when you refresh your report. This is accomplished using a tool that makes calculation more efficient and speedier, and there's no additional cost in the calculation using DAX to measure of runtime.
• Other scenarios These are not designed to calculate the age of a person based on their birth date. It can be used to calculate the time of inventory on products and also the various dates and dates from one other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc with a concentration on Computer engineering. The engineer has over 20 years' experience in the fields of data analysis, BI, databases, creating, and programming mostly using Microsoft technologies. He was a Microsoft Data Platform MVP for nine years in a row (from 2011 to the present) for his passion for Microsoft BI. Reza has been a prolific writer and co-founder of RADACAD. Reza is also co-founder and organizer of the Difinity event in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He has written a few books on MS SQL BI and also is writing various other books. He was also an active member of online technical forums such as MSDN and Experts-Exchange and was moderator on the MSDN SQL Server forums, and is an MCP and an MCSE as well as an MCITP for BI. He is also the lead of the New Zealand Business Intelligence users group. The group is also the creator of the book very well-loved Power BI from Rookie to Rock Star, which is totally free and comes with over 1700 pages as well as a companion book called Power BI Pro Architecture published by Apress.
It is an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user group. And He is a Microsoft Certified Trainer.
Reza's goal is to help users find the best solutions to manage data. Reza is a Data enthusiast.This post was filed with Power BI, Power BI from Rookie to Rockstar, Power Query and is categorized under Power BI, Power BI from Rookie to Rock Star, Power Query. The following is an excellent site to bookmark.

Post navigation

Share Different Visual Pages through different Security Groups. PowerBIAge in Years Calculation , which works for Leap Year in Power BI by using Power Query