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 used language usedin several functionsin Power BI, many users do not understand the functionality available in Power Query. In this article, I'll explain how easy it is to calculateAge within Power BI using Power BI. It is a great methodis very beneficial for situations where the computation of an agecan be carried out on a pre-calculated row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table that is included in the AdventureWorksDW table with an age column. I've removed a few of the extra columns in order to make the table simpler to be read.

In order to calculate how old each consumer, all you need is to:

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

That's about it. It can calculate an amount that is equal to the sum of the Birthdate column as well as the current date as well as time.

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

Duration

Duration is a particular kind of data format that is found with Power Query which represents the difference in two DateTime values. Duration is composed comprising four figures:

days.hours.minutes.seconds

This is what you will observe in the following values. But, from a person's standpoint, they shouldn't be required to find specifics similar to those above. There are ways to find every moment of time. With the Duration menu choice, you'll be able to see the range of seconds to minutes, hours days and years out of it.

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

The duration is calculated in days . It is then subdivided by 365, giving you the yearly value.

Rounding

No one has claimed they are 53.813698630136983! They state it as 53 with the rounding down. It is easy to select the Rounding option . You can then round down using the Transform tab.

This will let you know how old you are:

It's then possible to cleanse other columns, should you wish (or you could have made use of transformations under the Transform tab to prevent making new columns) You could name this column Age.

Things to Know

  • Refresh: The age calculated by the method can be refreshed every time you refresh your database. Every every time, it'll be comparing your birthdate with the date and date at the time of refresh. This method is the pre-calculation of your age. If you, however, need the age calculation to be executed dynamically using DAX here is how I explained the method you can use.
  • The reasons to use Power Query: Benefits of doing age calculations using Power Query is that the calculation is performed when you refresh your report. This is done by making use of a tool that makes calculation much simpler, and there's no need to incur the additional cost of doing it using DAX as a measure of time.
  • Another scenario is that this isn't used to calculate the date of birth as the age. This could be used for the inventory of items as well as to determine the difference between two dates and dates from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc in Computer engineering. The engineer has over 20 years' experience in data analysis database programming, BI, development and programming specifically focused specifically on Microsoft technologies. He is a Microsoft Data Platform MVP for nine years in a row (from 2011 until now) due to his dedication for Microsoft BI. Reza has a long-running career as a author and co-founder of RADACAD. Reza is also the co-founder and co-organizer for the 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 has written a few books on MS SQL BI and also is working on other books. He was also a frequent participant in online forums for technical matters like MSDN and Experts-Exchange , as well as moderator of MSDN SQL Server forums, as well as an MCP and the MCSE, as well as the MCITP for BI. The director is of the New Zealand Business Intelligence users group. Also, he's author of the highly popular title Power BI from Rookie to Rock Star, which is available for download for free and includes more than 17000 pages of content and another book titled Power BI Pro Architecture published by Apress.
Speakers are an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday in addition to SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's main goal is to assist 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 closely related to Power BI, Power BI from Rookie to Rock Star, Power Query. This article is an excellent resource to save to your bookmarks.

Post navigation

- Share different visual pages using different security groups of Power BIAge's Year Calculation that works for Leap Year in Power BI with Power Query

Comments

Post a Comment

Popular posts from this blog

Parts Per Million (ppm) Converter

Converter Parts Per Million (ppm) Parts per Million (ppm) Converter for gases. The converter calculates what the quantity of gas's volume as measured in ppm units and converts it into unit [mg/m 3[mg/m 3] and in reverse. The unit ppm can be used in many branches in a variety of ways. The use of ppm should be specified in the fields listed below to input, to ensure that it is transformed into the correct unit. For more details on the use of ppm, take a look at the guide below.In the field called "Molecular Weight, you will see the entry field where that field. Molecular Weight can be selected from the drop-down menu , or input the molecular weight in of the gaz. In case the weight of the molecules isn't well-known to you, then check out our molecular weight Calculator.When you input the formula or one of the and the formulas, either the formula in units of [ppm] or units of [mg/m3] or [mg/m 3[mg/m] 3. The value is converted instantly and displayed i...
Read more

Calorie Calculator

Calorie Calculator The Calorie Calculator can be used to calculate the number of calories an individual needs to consume each day. This calculator will also give fundamental guidelines for either gaining or losing weight. Food Energy Converter This converter could be utilized to convert Calories as well as other units used for energy consumption in food. Related:BMI Calculator | Body Fat Calculator | Ideal Weight Calculator That Calorie Calculator has been built on a variety of equations, and the calculations of the calculator are based on an approximate average. That Harris-Benedict Equation was one of the first equations to be utilized to calculate the basal metabolic rate (BMR), which corresponds to the amount of energy which is used up daily in a resting state. The equation was revised in 1984 to become more accurate and was used through 1990, at which it was at that point that when the Mifflin-St Jeor Equation was introduced. It is the Mifflin Saint Jeor Equa...
Read more

power-converter

Converter Parts Per Million (ppm) Parts Per million (ppm) Converter for GasesThis converter calculates what is the estimated amount (in increments of [ppmin units of) in order to convert it into an equivalent unit of measurement [mg/m 3. [mg/m 3] ] and vice versa and reverse. The unit ppm could be used in different branches in various ways. The use of ppm has to be described in the input fields below and in the manner it can be converted into the proper unit. For more information about the meaning of ppm examine the definition below.In the fields for input in the field for Molecular Weight, you can select the drop-down box or enter your own number in the field for molecular-weight of the gas. If the molecular weight of the gas is not something you know, we recommend using our molecular weight Calculator.When you enter either or both or you enter the formula in units of ppm or in units of (mg/m3) 3 or [mg/m3] the amount is instantly converted and then displayed in the...
Read more