Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, because DAX is the largest and most used language usedin numerous computationsin Power BI, many are unaware of this feature within Power Query. In this blog post I will explain how simple the process is calculateAge within Power BI and Power BI. The methodis exceptionally helpful when you need for calculate the the calculations of age.can be carried out on a row-by row basis.

Calculate Age from a date

Here is the DimCustomer table, which is made up of AdventureWorksDW table, which is the birthdate column. I've removed some of the columns that aren't required for clarity;

To calculate the average age of every client will be, all you'll need to do is to:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window, select the first column, birthdate.
  • Click on the Add Column Tab Then, under the "From Date & Time" section, then under Date Select the age range.

This is all there is to it. This is the method to calculate what is the ratio between the birthdate column, aswell being the current date and time.

However, the age shown on the Age column, but it doesn't appear to be an age. This is because it's an actual length.

Duration

Duration is a special type of data used by Power Query which represents the differences in Two DateTime values. Duration is composed of four numbers:

days.hours.minutes.seconds

This is how to understand the above numbers. However, from the perspective of the user it is not expected of them to comprehend the details of this. There are methods that can make each section of time. Selecting the Duration menu you'll be able to see the number of seconds, minutes, hours as well as years.

To assist to assist calculating the age in years for instance it is easy to select Total Years.

It is important to understand this: the measurement is in day, and afterwards divided by the number of days, to provide you with the value for the year.

Rounding

At the final point, nobody declares that they are 53.813698630136983! They state 53, which is rounded down. It's very easy to select Rounding, and then the round down option from the Transform tab.

This will show you the age of the years:

Then, you can clean the other columns as you'd like (or perhaps it's that you utilized transformations in the Transform tab to avoid the creation of new columns) This column can be renamed as Age the column.

Things to Know

  • Refresh The age calculated in this manner will be updated as you refresh your data. Each time, it will align the birth date with the date and the time when the data refresh occurred. That method can be described as an algorithm used for pre-calculating the age. If you need the calculation of age to be done dynamically using DAX This article will explain the method to make use of.
  • What's the reason? Power Query benefits of age calculation with The benefit of using age calculation with Query is that the calculation is performed during the refresh of your report. It's performed with an application that facilitates the calculation, and there is no extra cost to calculate it using DAX as a measurement of the runtime.
  • Additional scenarios This is not a method to calculate age just by birthdate. This can be used to calculate the stock age for products and also the distinctions between dates and times that are different from one.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc of Computer engineering. He has more that twenty years old and has worked in the field of data analysis and BI, databases, programmingand development, mainly using Microsoft technologies. He was an official Microsoft Data Platform MVP for nine years (from 2011 until now) for his commitment in Microsoft BI. Reza is an incredibly prolific writer and is co-founder with RADACAD. Reza is also the co-founder as well as co-organizer of the 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 has also written a few books in the field of MS SQL BI and also is working on other books. He was also a regular member of technical forums on the internet such as MSDN and Experts-Exchange and was the moderator on the MSDN SQL Server Forums, and holds the MCP and MCSE , as and the MCITP for Business Intelligence. He is the head for the New Zealand Business Intelligence users group. In addition, he's creator of the highly well-respected volume Power BI from Rookie to Rock Star, which is completely free and comprises nearly 17000 pages of information and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
The speaker is an International speaker at 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 objective is to help you find the most efficient data solution. He is an avid Data enthusiast.This post was published in Power BI, Power BI from Rookie to Rockstar, Power Query and was tagged with Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. You can follow any responses to this entry through the RSS feed.

Post navigation

Share different visual pages using various security groups within the Power BIAge's Year Calculation that is used for Leap Year in Power BI using Power Query

Comments

Post a Comment

Popular posts from this blog

Image
What's an angle ? Acute, obtuse angles An angle is the shape created by two rays who share a starting point, called vertex. vertex. You might be wondering what are the benefits of angles? You can calculate the building's height by knowing its distance from you, as well as the angles between top of the tower and ground. Through the same method you can calculate the size of the moon and, if you have proper tools, the circumference of our planet. Also, if you throw something and wish to determine how far it'll travel, you'll need to be aware of the angle that you throw it. There are many other fields that require angles . However, for the time being let's look at the fundamentals of geometry. Angles can be divided by their dimensions: WHAT IS A FREQUENCY CONVERTER ? A frequency converter, often referred to as a power-frequency converter, is a device that accepts the power of the source, usually at a frequency of 50 or 60 Hz, then converts it into a high-fre...
Read more
  What is One Thousand? Would you choose to do if had a thousand dollars?   You could want to purchase things like the PS4 and an  American Girl Doll .   But do you know what one thousand dollars is?   Are you aware of what the cashier is obligated to pay you back in exchange for paying by 1 000 dollars?   We must take a look at counting whole numbers to one thousand and backwards starting at one. counting in hindi one thousand  is shown as 1,000.   You would have to count one thousand whole numbers , starting at zero and ending with one thousand, the same way you count ten complete numbers that count from 0 to ten.   One can define a  full   number is every number that is not either decimal or fractional. For instance: 1, 2, 3, 4, 5 6 7, 8, 9, 10 are the entire numbers from 0 to 10. You could easily count those numbers down to ten very quickly.   I bet you could even count up to one hundred.   But,  counting whole nu...
Read more

reactiveEnergy-converter

Image
What is An angle ? It is a term used to describe angles which are sharp, acute, or narrow. A shape is made by two rays having the exact same starting point that is known as vertex. vertex. You can ask what are the advantages of angles? It is easy to determine what size to a building once you know the distance away it is from your, as well as the angle from the top of the tower and the ground. This same method is a way can determine how big the moon actually is or, if you have the right tools, estimate how much circumference we have of our earth. Should you toss something and you'd like to know the distance it travels , it is important to take note of the angle that you throw it at. There are numerous other scenarios where angles are beneficial however at this point we'll concentrate on the basics to geometry. Angles can be classified by their size: WHAT IS A FREQUENCY CONVERTER ? Frequency converter also known by the name of a power frequency convertor . It is a gadg...
Read more