Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. However, because DAX is the preferred languagein various researchesin Power BI, many are not aware of the feature available in Power Query. In this blog I'm going to demonstrate how easy to calculateAge in Power BI using Power BI. The methodis exceptionally effective in situations where the age calculationcan be done on an already calculated row-by-row basis.

Calculate Age from a date

That's the DimCustomer table that comes from the AdventureWorksDW table which as an age column. I've removed the columns that aren't needed for easier reading;

If you want to calculate the average age of your customers the only thing you need do is:

  • In Power BI Desktop, Click on Transform Data
  • In the PowerQuery Editor window, choose the Birthdate column first.
  • Click on the Add Column Tab. Under the "From Date & Time" section, under Date select the age range.

This is it. It can calculate an amount equal to the sum of the column for Birthdate column, in addition to the current date and hour.

But, the age displayed under the Age column doesn't seem to be an actual age. This is because it's a duration.

Duration

Duration is a special data type that is found inside Power Query which represents the distinctions between two DateTime values. Duration is a combination of four values:

days.hours.minutes.seconds

This is how you consider the data above. But from users' perspective you shouldn't expect them to go looking for specifics like this. There are methods to find each component using the length. If you choose the Duration menu it will reveal that you can get the number of seconds days, minutes, days and years from it.

To apply to use the method to use the method of calculating the age in years like, for example you can choose Total Years.

Make note of the fact that the duration of the program is calculated by days. This is then divided by 365, which will give you the yearly value.

Rounding

To conclude, there is no consensus that says that the years of age are 53.813698630136983! They say 53, with a rounding down. It is possible to select Rounding and Round Down in the Transform tab.

This will give you an estimate of your age in years:

After that, you can tidy up the other columns if you'd like (or maybe you've applied transformations through the Transform tab to avoid the column creation) and name this column: Age:

Things to Know

  • Refresh: The age calculated by this method shall be updated at the time that your data is refreshed. Every time, the system will compare with the birth date to the date and date that the data refresh occurred. It is a method allows for an earlier age calculation. If you want calculations of the age to be done in a dynamic manner using DAX Here's how I described how to use it.
  • How to utilize Power Query Benefits of calculating age in Power Query are that the calculations are made as you refresh your report. The calculation is made making use of an instrument that makes the calculation simpleand less complicated, and there won't be additional work involved in doing the calculation using DAX to measure runtime.
  • Additional scenarios used to determine age by birth date. This is a useful method to measure inventory for products as well as to distinguish two dates or dates each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc from Computer engineering. There are more than 20 years' working experience in data analysis database, BI, programming, and development predominantly using Microsoft technologies. He has been named an official Microsoft Data Platform MVP for nine years in a row (from 2011 until the present) for his passion for Microsoft BI. Reza is an incredibly prolific writer and co-founder of RADACAD. Reza is also co-founder and co-organizer of the Difinity conference at 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 also written a few books about MS SQL BI and also is writing several more. He was also a regular participant in online forums for technical issues , such as MSDN and Experts-Exchange . He also served as the moderator for MSDN SQL Server forums, with the MCP as well as MCSE as well as an MCITP in BI. He is the Director of the New Zealand Business Intelligence users group. He is also the co-author of the book highly praised Power BI from Rookie to Rock Star, which is free and includes more than 700 pages of material, as well as The Power BI Pro Architecture published by Apress.
This speaker has been an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, as well as SQL group for users. And He is a Microsoft Certified Trainer.
Reza's desire is to help users find the most effective data solution. He is a Data enthusiast.This blog post has been released under Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic resource to bookmark.

Post navigation

Share Different Visual Pages by utilizing different security groups in Power the BIAge's age Calculation which is used to calculate the Leap Year in Power BI with Power Query (see below).

Comments

Post a Comment

Popular posts from this blog

How to Convert Your Pace to a Race Pace

Image
Introduction: Pace is an important aspect of any race. If you want to win, you have to keep up with the other runners. But if you're trying to outpace someone, you'll need to set a pace that allows your body to function at its best. Here's how to do it: How to Convert Your Pace to a Race Pace. Pace is the pace at which a runner can complete a set distance in a given amount of time. A runner’s pace is determined by their running speed and how much they are willing to push themselves. When you set your pace, you are setting the specific distance that you will run in a certain amount of time. Thisdistance can be in miles or kilometers. The easiest way to convert your pace to a race pace is to use an online Pace Calculator. Another way to convert your pace to race speed is by using a Runner’s Gear Pace Meter. This meter works very similarly to an electronic watch and allows you to track your running speed and duration on-the-go. How to Convert Your Pace to a Race Pace. If yo...
Read more

Each Converter is the go-to-source for all your converting needs!

Image
  Welcome to Each Converter! We are the go-to source for all your converting needs. Whether you need to convert length, weight, time, or something else entirely, we have the tools and resources you need. We are always updating our site with the latest converters and calculators, so you can be sure you're getting the most accurate information. Thanks for visiting Each Converter! If you're looking for a converter, you've come to the right place! Each Converter is the go-to-source for all your converting needs. We have a wide variety of converters to choose from, so you're sure to find the perfect one for your needs. We have converters for everything from length and weight to currency and temperature. Whatever you need to convert, we have a converter for it! We even have a converter for converting between different units of measure. Our converters are easy to use and accurate. Just enter the value you want to convert and select the units you want to convert it to. That...
Read more

Speed Converter - Speed up your internet connection!

Image
Introduction: Speed Converter is a great tool for improving your internet connection. It can help you save time and improve your internet experience. Speed Converter can also be used to improve your website speed. You can use Speed Converter to fix problems with your internet connection, speed up your website , or optimize your web page for faster loading times. How Speed Converter Works. Speed Converter can help you speed up your internet connection by translating files into different speeds. It can also help you save time on your internet connection by identifying and fixing issues with your current internet connection. In other words, Speed Converter can help you improve your internet experience in a hurry! How Speed Converter Can Help You Speed Up Your Internet Connection. Speed Converter can help you speed up your internet connection by identifying and fixing issues with your current internet connection. For example, it can identify problems with the modem, router, or service pr...
Read more