Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, because DAX is the most popular language usedin several functionsin Power BI, many don't know the functions available in Power Query. In this article I will discuss how simple it is to calculateAge in Power BI in conjunction with PowerBI. This methodis extremely beneficial in cases where the calculation of the agecan be calculated on a row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table, which is one of AdventureWorksDW table and is an aged column. I've eliminated a few additional columns in order to make the table simpler to navigate.

To calculate the age and gender of 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 selecting the Birthdate column.
  • Click on the Add Column Tab, which is under the "From Date & Time" section. Under Date select the age range.

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

However, the appearance of the age in the Age column, but it doesn't really appear like an age. It is due to the fact that it is the duration.

Duration

Duration is a specific kind of data structure that can be found inside Power Query which represents the distinction in two DateTime values. Duration is a combination of four numbers:

days.hours.minutes.seconds

You will discover in these values. But, from a person's viewpoint, they shouldn't have to find specifics similar to the ones mentioned above. There are ways to get every part of the time. Utilizing the Duration menu option, you'll be able to see the range of seconds to minutes, hours, and years out of it.

To calculating the age in years like, for instance you just need to go on to Total Years.

The duration is calculated in days , then divided by 365, giving you the annual value.

Rounding

Also, nobody claims that they're 53.813698630136983! They say 53, with a rounding down. You can choose the Rounding option and round down the Transform tab.

This will give you an indication of how old you are:

Then, you can purify other columns if desired (or you could have used transformations under the Transform tab to avoid creating new columns) It is possible to name this column as Age.

Things to Know

  • Refresh The age that is calculated by the method will be updated each time you refresh your database. And each time, it compares your birthdate to the date and timing at the time of refresh. This method is a pre-calculation of an age. If, however, you want the age calculation to be performed dynamically, using DAX here is how I described a way that you can make use of.
  • Why you should choose Power Query: Benefits of performing calculations on age in Power Query is that the calculation is made at the time of refreshing your report. This is accomplished using a tool that makes calculations easier, and there's no need for the extra cost of using DAX as a measure of the runtime.
  • Another situation where this method is not utilized to calculate the birthdate as an age. This can be used to calculate inventory of products as and 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. He has more than 20 years of experience in data analysis database, BI, programmingand development primarily focused on Microsoft technologies. He has been a Microsoft Data Platform MVP for nine consecutive years (from 2011 to the present) due to his dedication for Microsoft BI. Reza is a prolific author and co-founder of 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 has written several books on MS SQL BI and also is working on other books. In addition, he was a frequent participant in online forums on technical matters like MSDN and Experts-Exchange as well as moderator of MSDN SQL Server forums, and is an MCP and an MCSE and MCITP for BI. As the group's director, he is in charge of New Zealand Business Intelligence users group. He is also the author of the highly acclaimed title Power BI from Rookie to Rock Star, which is available for download for free and includes more than 17000 pages of information and another book titled Power BI Pro Architecture published by Apress.
It is an International Speaker in 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 passion is to help you find the most efficient ways to use data, and he's an avid Data enthusiast.This article was published in 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 is a fantastic resource to save to your bookmarks.

Post navigation

- Share various visual pages using different security groups that PowerBIAge uses for Year Calculation that works for Leap Year in Power BI with Power Query

Comments

Post a Comment

Popular posts from this blog

what is cpu

  what is cpu Also known as the name for a central processor, or microprocessor, the CPU (pronounced"sea-pea-you") is the main processor (meaning that it is a cpu)of computers.   The CPU in computers handles all the commands it receives from programs in addition to operating the physical  runningm on the.   Computer.   This computer has processed, for instance instructions for the internet browser to open and show the web page on your computer.Tip The CPU is sometimes called"the"  the brain  which is also known as the computer.   However, it is more precise to refer to software as the brain. This is due to the CPU acting as an efficient calculator.   The CPU is exceptionally skilled in math, however, without software, it would not be able to do anything else.Note cpu full form  (  central processing unit  ) Many computer users who are new may wrongly refer to their computers as well as often, the screen being referred to as the...
Read more

Parts Per Million (ppm) Converter

Converter Parts Per Million (ppm) Parts Per Million (ppm) Converter for gases The converter calculates what volume of gas measured in ppm units, and converts it to quantities [mg/m 3] and as inverse. The unit ppm is employed in a variety of branches and ways. The use of ppm therefore should be mentioned in the fields below to input, in order to determine the way it will be converted to the correct unit. For more information on the use of ppm take a look at the instructions below.In this area, the entry field for is the Molecular Weight you could either choose from the drop-down menu or simply enter the molecular weight for the chemical. If you are unsure of the molecular value to you, then you should check out our Molecular Weight Calculator.When you enter your formula that you enter, either formula or the formula in units of [ppm] or in units of [mg/m(mg/m). 3. The value is converted immediately and is displayed at the lower right. This is how the importance o...
Read more

Calorie Calculator

Calorie Calculator The Calorie Calculator can be used to determine the number of calories an individual has to consume per day. The calculator can also provide the basic guidelines to gain or losing weight. Food Energy Converter The converter can be utilized to convert Calories and other common units for energy content in food. Related:BMI Calculator | Body Fat Calculator | Ideal Weight Calculator That Calorie Calculator was founded on several equations and its results are based upon an estimated average. It is believed that the 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 is utilized daily at rest. The equation was revised in 1984 to make it more precise and was utilized through 1990, at which it was at that point that when the Mifflin-St Jeor Equation was introduced. In addition, the Mifflin Saint Jeor Equation will also calculate BMR and has been proved to be...
Read more