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, a lot of people don't recognize the feature available in Power Query. In this article, I'll show how easy it can be to calculateAge in Power BI and Power BI. It is a great methodis extremely helpful for situations where the calculation of an agecan be carried out on a pre-calculated row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table, which can be found in the AdventureWorksDW table which includes the age column. I've removed a few extra columns to make it easier to comprehend.

To calculate how old every consumer, all you have to do is to:

  • In Power BI Desktop, Click on Transform Data
  • On the Power Query Editor window; start by clicking on the Birthdate column.
  • Click on the Add Column Tab located under the "From Date & Time" section, and under Date select the age range.

It's that simple. it. This can calculate an amount that is the sum of the Birthdate column as well as the present date, as well as the time.

However, the age that appears within the Age column, does not seem to look like an age. That is because it is an actual duration.

Duration

Duration is a special kind of data structure that can be found inside Power Query which represents the difference in two DateTime values. Duration is composed from four different numbers.

days.hours.minutes.seconds

It's exactly what you'll see in the above values. In reality, from an individual's point of view, they shouldn't be expected to find specifics similar to the ones listed above. There are ways to make sense of all the time. Utilizing the Duration menu option, you'll be able to see the range of seconds, minutes, hours, days and years out of it.

For calculating the age in years such as you only need to go to Total Years.

The duration is measured by days, then it is divided by 365, giving you the value for the year.

Rounding

Also, nobody claims that they're 53.813698630136983! They state it as 53 with a rounding down. You can choose the Rounding option , then round down from the Transform tab.

This will show you your age in years:

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

Things to Know

  • Refresh The age determined by this method will be updated each time you refresh your data. Every time, it will check your birthdate to the date and moment of the refresh. This method is a pre-calculation of an age. If, however, you need to have the calculation be done dynamically employing DAX, here's how I described an approach can be used.
  • The reasons to use Power Query: Benefits of performing age calculations in Power Query is that the calculation is performed during the process of refreshing your report. It is accomplished by making use of a tool that simplifies calculation, and there's no need to spend the additional expense of doing the calculation using DAX as a measure of running time.
  • Another scenario is that this isn't utilized to calculate the age of a person based on their birth date. It could be used for inventory of goods as also for the differences between two 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 holds the BSc of Computer engineering. The engineer has over 20 years' experience in data analysis , database programming, BI, development and programming specifically focused on Microsoft technologies. He is an official Microsoft Data Platform MVP for nine years (from 2011 until today) because of his dedication towards Microsoft BI. Reza is known as a prolific writer and is co-founder with RADACAD. Reza is also co-founder as well as organizer of 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 other books. He is also a regular participant in online forums for technical matters like MSDN and Experts-Exchange , as well as moderator of MSDN SQL Server forums, and is an MCP as well as an MCSE. He is also MCITP for Business Intelligence. The director is of the New Zealand Business Intelligence users group. He is also the author of the incredibly popular books such as Power BI from Rookie to Rock Star, which is available for download for free and includes more that 17000 pages of information . It also includes another book titled Power BI Pro Architecture published by Apress.
He is an International Speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday along with SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's main goal is to assist you find the most efficient ways to use data, and is an avid Data enthusiast.This blog post appeared 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 article is a good source to bookmark.

Post navigation

- Share Different Visual Pages by using different security groups of Power BIAge's Year Calculation that works for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

Random Number Generator

Random Number Generator: How Do Computers Generate Random Numbers? People have been using random numbers for millennia. Therefore, the concept isn't entirely new. From the lottery in ancient Babylon, to roulette machines in Monte Carlo, to dice games in Vegas the aim of the game is to let the final result to random chance. However, aside from gambling, randomness has many applications in the fields of science, statistics, cryptography and more. However, using coins, dice or other similar media as an random device comes with its own limitations. Due to its mechanical basis for these techniques, generating large quantities of random numbers will require a plenty of time and effort. Thanks to human ingenuity, we have more efficient techniques and tools that are available. Methods for generating random numbers True Random Numbers Photo of the analog input digital output processing device. Photo by Harrison Broadbent Let's consider two principal method...
Read more

Calorie Calculator

Calorie Calculator This Calorie Calculator can be used to calculate the number of calories an individual must consume every day. The calculator also provides the basic guidelines to gain or losing weight. Food Energy Converter The converter mentioned above can be used to convert Calories as well as other units used to measure energy in food. Related:BMI Calculator | Body Fat Calculator | Ideal Weight Calculator The Calorie Calculator can be built on a variety of equations, and the calculations of the calculator are based on an estimated average. The Harris-Benedict Equation was one of the first equations to be used to calculate basal metabolic rate (BMR) that corresponds to the amount of energy that is used up daily in a resting state. The equation was revised in 1984 to make it more precise and was utilized until 1990, at which it was at that point that it was replaced by the Mifflin-St Jeor Equation was introduced. It is the Mifflin Saint Jeor Equation will also...
Read more

power-converter

 <h1>  <a href="https://aboneapp.com/#/partsPer-converter">Converter Parts Per Million </a>(ppm) </h1> <figure>  <table><tbody><tr><td>      Parts per Million (ppm) Converter for gases. This <a href="https://aboneapp.com/#/pace-converter">converter</a> calculates an approximate amount (in the form that is [ppm]) in order to convert it to equivalent units (mg/m <sup>3.</sup>[mg/m 3] and in reverse. The unit ppm is utilized in a variety of branches in different ways. The use of ppm has been defined in the input fields below, based on the manner it can be converted using the appropriate unit. For more details on the use of ppm, read the explanation below.In this field, you can input for the molecular weight field. you can select the drop-down menu or type in your own value which represents the molecular mass of gas. If the molecular weight isn't evident to you, we suggest you try our...
Read more