Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method for calculating the age. However, as DAX is the primary language usedin numerous computationsin Power BI, many don't know about this function in Power Query. In this article, I'll describe how simple it is to calculateAge in Power BI using Power BI. This methodis very beneficial for situations where the estimation of your agecan be done as an earlier calculated row by row basis.

Calculate Age from a date

Below you can see the DimCustomer table that is part of the AdventureWorksDW table which functions as your birth date column. I've removed some of the columns that aren't needed in order to simplify the read.

To calculate your age for each customer the only thing you need to do is to:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; pick the Birthdate column first.
  • Go to the Add Column Tab, and then go to Column Tab, and then click"Add Column Tab," then click on the "From Date & Time" section. Then, under Date, choose Age

That's that. This is the method you calculate an amount which is the sum of the Birthdate column, together with the current date and time.

However, the age displayed under the Age column, and doesn't seem to be a real age. It's because it's actually a time period.

Duration

Duration is a special data type in Power Query which represents the variation between two DateTime values. Duration is the combination of four values:

days.hours.minutes.seconds

and that is how you look at the data. For the view of the user it is not necessary for them to research the specifics of this. There are methods that could be able to get every component of the period. By selecting from the menu of Duration, you'll be able to get the amount of seconds and minutes, hours, days and years out of it.

To assist with calculating the age in years such as, for instance it is easy to hit the Total Year:

Be aware that the duration is calculated in days . Then, it's divide by the total number of days, to yield the yearly amount.

Rounding

It's the truth, no person claims to be 53.813698630136983! They claim 53, which is rounded down. It's easy to select Rounding, and then round down in the Transform tab.

This will reveal your age in years:

Clean up the other columns, if you want (or maybe you've made use of transformations using the Transform tab to avoid having inventing new columns) Then, you can call this column"Age"

Things to Know

  • Refresh The age calculated this way will get changed each time you're refreshing your information. Each time, it will compare the birth date to the date and timing at the time of refresh. This method is a pre-calculation for an age. If you want the calculation to be done dynamically with DAX here's the method I used to describe the method that you can use.
  • The motivation for Power Query: Benefits of doing an age calculation with Power Query is that the calculation is made in the course of refreshing the report. The report is refreshed using an instrument that makes the calculation easier and faster, and there's no extra cost when it is calculated using DAX to gauge the time.
  • Alternative scenarios It cannot be used to calculate the date of birth. This could be used to calculate the inventory-level age of items and also the difference between two dates or times from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds the BSc in Computer engineering; he is more than twenty years' experience in the field of data analysis and database programming, BI and development with a focus on Microsoft technologies. He was an official Microsoft Data Platform MVP for nine years running (from 2011 until today) because of his love of Microsoft BI. Reza is a prolific blogger, and the co-founder and the editor for RADACAD. Reza is also the co-founder and co-organizer for Difinity Conference. Difinity Conference located 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 wrote several books on MS SQL BI and also is working on additional books. He also was a regular participant in online forums for technical issues such as MSDN and Experts-Exchange and was also moderator of MSDN SQL Server forums, and is an MCP and MCSE and an MCITP of Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. In addition, he is the creator of the well-known publication Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and A Press publication 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 users groups. And He is a Microsoft Certified Trainer.
Reza's main goal is helping users find the perfect data solution. He is a Data enthusiast.This article was published by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is filed within Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. Please leave a comment.

Post navigation

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

Comments

Post a Comment

Popular posts from this blog

angle-converter

Image
What is An angle ? The angles that can be acute, sharp,, or narrow. An angle is a form created by two rays. Both have a common point of origin, called the vertex . One might wonder why angles crucial? It is possible to estimate the dimensions of a tower if it is known the length between you and it, and also angles between the walls and the top of the tower. By using the exact method it is possible that you find out how big the moon is, or with the appropriate tools, the radius of our planet is. In addition, if you throw something and are trying to determine the distance it's going to travel, it is necessary to look at the angle from which the object was being thrown from. There are many other fields which require angles, but in the present, let's concentrate on basic geometry. Angles are classified based on their dimensions: WHAT IS A FREQUENCY CONVERTER ? The frequency converter can be described as a the power frequency convertor. It's a gadget that absorbs the...
Read more

energy-converter

Image
What's the meaning behind an angle ? The angle can be acute, obtuse or narrow. An angle is a shape made by two Rays. Both have a similar starting point, which is known to be the vertex. It is a matter of what are the advantages of angles? You can estimate the top of a tower, if you know where away it is from the location you live in and the angles between the top of the tower to the summit of the tower. If you employ a similar techniques, it is possible to are able to estimate the size the moon or If you're equipped with the right equipment, it is possible to determine the circumference that our earth. If you throw something and you want to know the distance it travels, you'll have to know the angle at which your throw is. There are a variety of fields where angles can be useful, but for now we'll focus on the basics of geometry. Angles are classified according their dimensions: WHAT IS A FREQUENCY CONVERTER ? Frequency converters, also known as power-freque...
Read more