Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, as DAX is the most popular language usedin several functionsin Power BI, many do not realize the function available in Power Query. In this article I will describe how simple it is to calculateAge in Power BI with Power BI. This methodis extremely beneficial for situations when the computation of an agecan be performed on pre-calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table that is part of the AdventureWorksDW table, which is an age column. I've removed some of the extra columns to make it easier to read.

In order to calculate the age of every consumer, all you need is to:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; start by selecting the Birthdate column.
  • Click on the add Column Tab, which is under the "From Date & Time" section, and under Date, choose the age range.

That's all there is to it. This can calculate an amount that is the sum of the Birthdate column and the current date as well as time.

But, the age appears within the Age column, does not really look like an age. That is because it is a duration.

Duration

Duration is a special kind of data format found in Power Query which represents the difference between two DateTime values. Duration is a mix of four numbers:

days.hours.minutes.seconds

This is what you will see in the above values. However, from an individual's perspective they shouldn't be expected to search for specifics like the ones above. There are methods that could get every part of the time. using the Duration menu option you'll notice the amount of seconds to minutes, hours days and years out of it.

For calculating the age in years such as, for instance you just need to go to Total Years.

The duration is calculated in days and then divided by 365 to give you the yearly value.

Rounding

Finally, no one claims they are 53.813698630136983! they say it as 53, with a rounding down. You can easily choose the Rounding option and then round down on the Transform tab.

This will show you your age in years:

It is then possible to clean other columns, if desired (or you could have made use of transformations in the Transform tab to prevent making new columns) You can name this column: Age.

Things to Know

  • Refresh: The age calculated by this method will be updated every time you are refreshing your dataset. And each time, it will compare your birthdate with the date and time at the time of refresh. This method is a pre-calculation of an age. If you, however, need the age calculation to be carried out dynamically, using DAX here is how I described a way that you can make use of.
  • Reasons to choose Power Query: Benefits of performing age calculations in Power Query is that the calculation is performed at the time of refreshing your report. This is done by using a tool that makes calculation easier, and there's no need for the extra expense of calculating it using DAX as a measure of runtime.
  • Another scenario This is not used to calculate the age by birthdate. This could be used for inventory of products as well as the difference between two dates and times from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds the BSc of Computer engineering. He has more than 20 years of experience in data analysis database, BI, programmingand development primarily focused on Microsoft technologies. He is a Microsoft Data Platform MVP for nine years in a row (from 2011 until now) due to his dedication to Microsoft BI. Reza is a prolific writer and is co-founder with 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 wrote some books on MS SQL BI and also is writing some others, He was also a regular participant in online forums for technical issues such as MSDN and Experts-Exchange as well as moderator of MSDN SQL Server forums, and is an MCP and MCSE as well as an the MCITP for BI. He is the director of the New Zealand Business Intelligence users group. Also, he's the author of very popular book Power BI from Rookie to Rock Star, which is available for free and has more that 17000 pages of information and an additional book called Power BI Pro Architecture published by Apress.
The speaker is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, and SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's passion is to help you discover the most effective solutions for data, and he's a Data enthusiast.This article was published in Power BI, Power BI from Rookie to Rockstar, Power Query and related to Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great resource for you to bookmark.

Post navigation

- Share Different Visual Pages by using Different Security Groups in Power BIAge's Year Calculation that works for Leap Year in Power BI with Power Query

Comments

Post a Comment

Popular posts from this blog

All Vegetables Name In English

  All Vegetables Name   In English-Hindi   Vegetable Name   (sbjiyo ke naam) All Vegetables Name  In English-Hindi  Vegetable Name  (sbjiyo ke naam).   Friends, we all know that all people eat vegetables.   There's not a single person who does not.   The vegetable is the most important ingredient in many mealsand are very beneficial to our health. There are a myriad of  vegetables name  that are grown and eaten all over the globe.   We are familiar with the names that are used to describe  several  varieties of these fruits and vegetables, however, we're not aware of the names of the various varieties.   This is because certain kinds of vegetables are only found in certain regions or countries and others are available everywhere in the world. Children who are either in 1st grade or 2nd grade or the second grade are  mandated  to learn names of vegetables right from the beginning.   This will a...
Read more

power-converter

Image
What is an angle ? An angle that is acute, narrow, or narrow. An angle is a shape created by two rays that share the same starting point, which is known as vertex. vertex . Some may wonder what are the advantages of angles? It is possible to calculate the height of a building if you have the right distance to the building, as well as angles between the sides and the top. Utilizing the same method, then you can determine how large the moon's size and, if you have the right tools how big the circle of our own planet. Additionally, if it's the intention of throwing something that you want to know the distance it will travel, you must determine the angle of the spot that you're throwing it. There are a variety of areas where angles are crucial, but we'll look at the basics of geometry. Angles are classified based on their size: WHAT IS A FREQUENCY CONVERTER ? A frequency converter is commonly called a power frequency converter . It takes energy from an input sou...
Read more

Why are BMIs useful ?

Calculate your BMI Utilize our BMI calculator to see how your weight is healthy in relation the height. Body Mass Index ( BMI ) determines your weight in relation to your height. It could be a helpful indicator to determine whether your weight is within an, below, or within the range of a healthy. Use our BMI Calculator tool to find out how health your weight is in relation to your height.https://www.medibank.com.au/content/dam/livebetter/en/calculators/bmi.html What does the result mean? BMI Chart for Australia BMI Range Category Below 18.5 Underweight 18.5 - 24.9 Healthy weight 25.0 - 29.9 Overweight 30.0 and above Obese If your BMI is within 18.5 between 18.5 and 24.9 , it indicates you're in that safe overweight range. Source: The World Health Organization Two adults who are exactly the same heig...
Read more