Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, because DAX can be the largest and most widely used language usedin several functionsin Power BI, a lot of users don't recognize the feature available in Power Query. In this article, I'll explain how easy it is to calculateAge within Power BI using Power BI. This methodis extremely useful when the calculation of the agecan be performed on pre-calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table that's part of the AdventureWorksDW table and is an aged column. I've removed some of the extra columns so that it is simpler to comprehend.

In order to calculate an age range for each person who purchases from you, all that you have to do is:

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

This is all there is to it. This is able to calculate an amount that is equal to the sum of the Birthdate column and the actual date and time.

But, the age appears within this Age column, does not actually look like an age. It's because it's the duration.

Duration

Duration is a special kind of data format found with Power Query which represents the distinction in two DateTime values. Duration is a mixture of 4 numbers.

days.hours.minutes.seconds

This is what you will discover in these values. But, from a person's view, they shouldn't need to seek out specifics such as the ones listed above. There are methods that could capture every second of the time. When you select the menu option Duration, you'll be able to see the range of seconds to minutes, hours, days and years.

For calculating the age in years like, for example, you need to go to Total Years.

The duration is determined by days and then divided by 365 in order to get the yearly value.

Rounding

Also, nobody claims to be 53.813698630136983! They say 53 with the rounding down. You can simply select the Rounding option and then round down the Transform tab.

This will let you know how old you are:

It's then possible to remove other columns in case you'd like (or you could have made use of transformations in the Transform tab to stop creating new columns.) This column can be named. column Age.

Things to Know

  • Refresh The estimated age by the method shall be updated each time you are refreshing your data. Each every time, it'll be comparing your birthdate to the date and date at the time of refresh. This method is the pre-calculation of your age. If you require that the calculation be dynamically performed, with DAX, this is how I have described a technique you can use.
  • Reasons to choose Power Query: Benefits of calculating age in Power Query is that the calculation is done during the refresh of your report. It is accomplished by using a tool which makes calculations much easier and there's no need for the extra cost of using DAX to measure running time.
  • Another scenario This is not employed to calculate the age of a person based on their birthdate. This could be used for the inventory of items as and the difference between two dates or times each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering. The engineer has over 20 years' expertise in the field of data analysis, database programming, BI, and development mostly focused in Microsoft technologies. He is a Microsoft Data Platform MVP for nine years (from 2011 until now) due to his devotion to Microsoft BI. Reza is an incredibly prolific writer and is co-founder with RADACAD. Reza is also co-founder and director 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 a couple of books about MS SQL BI and also is working on other books. He was also a frequent participant in online forums on technical issues like MSDN and Experts-Exchange . He was also a regular participant as moderator for MSDN SQL Server forums, and is an MCP , an MCSE and MCITP for BI. As the group's director, he is in charge of New Zealand Business Intelligence users group. Additionally, he is the writer of the highly acclaimed title Power BI from Rookie to Rock Star, which is available for download for free and includes more that 17000 pages of content and another book titled 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 along with SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's love is helping you find the most efficient solutions for data, and is an avid Data enthusiast.This post was originally published by Power BI, Power BI from Rookie to Rockstar, Power Query and connected to Power BI, Power BI from Rookie to Rock Star, Power Query. This is a great resource to save to your bookmarks.

Post navigation

- Share Different Visual Pages using different security groups within 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

Scientific Calculator

Best Scientific Calculator for Engineering & Architecture Students in 2021 With the increase in the number of college students and professionals in this Engineering and Information Technology sector The need of scientific calculators is growing. Best scientific calculators for engineering students are nothing but electronic calculators that are typically hand-held and used to solve complex problems of math and science. QUICK COMPARISON: Our Top 3 Picks For Scientific Calculators There are several variations for the science calculator and any Engineering or science student will appreciate the use and the necessity of scientific calculators in their daily everyday. Best Scientific Calculator for Engineering Students To choose the ideal scientific calculator would be difficult to choose, but from the large variety of brands and calculators on the market, reviewing them in a brief manner would be useful. Casio FX-991EX Engineering/Scientific ...
Read more

Parts Per Million (ppm) Converter

Converter Parts Per Million (ppm) Parts Per million (ppm) Converter for gases This converter calculates an approximate amount (in the units [ppm[ppm]) Then it converts that number into units of (mg/m 3. [mg/m 3] ) and vice versa. The unit ppm is used in a myriad of branches in a variety of ways. The usage of ppm must to be identified in the fields below for input to ensure that it's changed to the proper unit. For more details on the use of ppm take a look at the following description below.In the input box into your Molecular Weight field, you can select from the drop-down menu or input your own quantity to compute the mass molecular of gas. If the value of the molecular weight is not clear to you take a look at our Molecular Weight Calculator.When you enter either or both or the calculation in the unit [ppm] in the units [mg/m 3 or [mg/m3The amount will be converted instantly and will be displayed in the lower part of the page. The importance of the data ...
Read more

Calorie Calculator

Calorie Calculator The Calorie Calculator can be used to determine the amount of calories that a person needs to consume each day. This calculator can also provide some basic guidelines for gaining or losing weight. Food Energy Converter This converter could be utilized to convert Calories and other common units to measure energy in food. Related:BMI Calculator | Body Fat Calculator | Ideal Weight Calculator The Calorie Calculator can be founded on several equations and its results are based on an estimate of an average. That Harris-Benedict Equation was one of the first equations to be used to calculate the basal metabolic rates (BMR) which corresponds to how much energy that is utilized daily at rest. It was updated in 1984 to make it more accurate , and was in use through 1990, at which time there was a change to the Mifflin-St Jeor Equation was introduced. In addition, the Mifflin Saint Jeor Equation will also calculate BMR, and has been proved to be more accu...
Read more