Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method for calculating the age. But, because DAX is the primary language usedin numerous calculationsin Power BI, many don't know about this feature in Power Query. In this article, I'll describe the process of how to calculateAge in Power BI with Power BI. This methodis extremely helpful in situations where your estimation of the agecan be done as an earlier calculated row-by-row row basis.

Calculate Age from a date

Below you'll find the DimCustomer table, which is a part of the AdventureWorksDW table that acts as the birthdate column. I've removed a few the columns in order so that it is easier look through.

In order to calculate your age for each of your customers All you have to do is:

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

That's it. this is the method you calculate the amount which is the total of the column for Birthdate column, as well as the current date and time.

The age, however, which appears in"age" in the Age column, and doesn't seem to be a true age. It's due to the fact that it's an actual duration.

Duration

Duration is a specific form of data that is part of Power Query which represents the difference between the two DateTime values. duration is a combination of four numbers:

days.hours.minutes.seconds

This is how you should look at the data. For the view of the user, it's not required of them to look up the specifics of this. There are techniques that can capture every single part of the time. If you choose the Duration menu, you'll discover that you can to extract the number of minutes and seconds as well as days, hours and years out of it.

To help in calculating the age in years such as, for instance the age of a person, simply click the Total Year:

The duration is measured in days . It is after that multiplied by days, to yield the yearly amount.

Rounding

In reality, no one says they're 53.813698630136983! They are 53, which is an arbitrary number that is rounded down. It is easy to select Rounding and round down on the Transform tab.

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

Clean up the other columns, if you want (or maybe you've taken advantage of transformations using the Transform tab, avoiding having make new columns) And name this column"Age:

Things to Know

  • Refresh The age that is calculated in this manner is updated every time you are refreshing your information. Every time, it will match dates of birth with the date and duration at the time of refresh. This method is a pre-calculation of an age. If, however, you require that the calculation be dynamically done using DAX this is the way I explained the method you could employ.
  • The reason behind Power Query: Benefits of doing an age calculation with Power Query is that the calculation is performed at the time of refresh of your report, and using an instrument which makes the calculation more efficient and speedier, and there is no additional overhead when calculating it using the DAX method to determine the time.
  • Alternative scenarios It cannot be used to calculate the date of birth. this can be used for an inventory level age calculation and also to determine the difference between dates and times from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc on Computer engineering. He has more than 20 years old. experience in the area of data analysis, databases, BI, and development mostly in Microsoft technologies. He was an official Microsoft Data Platform MVP for nine years running (from 2011, until now) for his passion with Microsoft BI. Reza is a prolific blog writer who is also the co-founder as well as director of RADACAD. Reza is also co-founder as well as 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 has written several books on MS SQL BI and also is writing additional books. He also was a regular forum participant on online forums for technical matters like MSDN and Experts-Exchange and was also the moderator of MSDN SQL Server forums, and holds an MCP and MCSE as well as an MCITP of Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. He is also the co-author of the well-known publication Power BI from Rookie to Rock Star, which is available for free and includes more than 1750 pages of information and 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 Users Groups. And He is a Microsoft Certified Trainer.
Reza's passion is to help users find the most effective data solution. He is an avid Data enthusiast.This article was written by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is filed into 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 Calculation in Years which works for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

Scientific Calculator

You're allowed to use a calculator for the portion of the Math Test--but there's more to it than simply making sure that you've got a new supply of batteries.Important: Bring Your Own Calculator Calculators aren't offered at the test centers, and you aren't able to share a calculator . Quick Facts Only the calculators mentioned on this page are accepted to be used for the Math Test, Calculator portion of the test. You may not use any calculator while working on tests like the Reading, Writing and Language, or Math Test--No Calculator portions. You will need to remove the calculator aside during these sections of the test. You will be dismissed and your marks canceled should you utilize your calculator for sharing details during the test or to remove questions or answers from the testing room. Calculator Smarts Bring your own calculator. You cannot share one. Do not bring a new calculator. Bring one you know. Practice fo...
Read more

angle-converter

Image
What is the definition of an angle ? It is a term that is used to describe angles that are sharp or acute. They can also be narrow. An angle is an shape formed by two rays that have a common point of origin known as the vertex . Many might wonder: Why are angles so important? You can determine the size of a building when you know the distance between you and it, and also angles between the surfaces just to the left of it. Utilizing the same method, there is a way to be able to determine the size of the moon is, or with the right tools, the diameter that our earth is. If you throw something and are trying to calculate the distance it's going to travel, you'll need to understand the angles that the object is hitting at. There are numerous other fields which require angles, however we'll focus on basic geometry. We can classify angles by their size: WHAT IS A FREQUENCY CONVERTER ? The frequency converter, also called a power-frequency converter, is one that takes p...
Read more

energy-converter

Image
What's the meaning behind an angle ? The angle may be acute or obtuse or narrow. An angle is a design that is created by two Rays. The two have a common beginning point , also known to be the vertex. There is a question: what are the advantages of angles? You can estimate the top of a tower , provided you know how far it is from your location and the angles between the top of the tower between the tower's top and surface. By employing similar methods, there is a possibility to calculate the size the moon. Or in the event that you're equipped with the appropriate equipment it will determine the radius of our planet. In addition, if you throw something and want to determine the distance it travels, then you'll need to be aware of the angle at which you throw it. There are many areas where angles can be beneficial, however, in the present, let's focus on the fundamentals of geometry. Angles are classified based on their dimensions: WHAT IS A FREQUENCY CONVERTE...
Read more