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 know the functions available in Power Query. In this article I will show how easy it can be to calculateAge within Power BI using Power. It is a great methodis extremely helpful in situations where the calculation of the agecan be completed on a pre-calculated row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table, which is included in the AdventureWorksDW table that is one of the columns that is a time column. I've removed some extra columns, to make it simpler to be read.

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

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

That's all there is to it. This will calculate an amount that is the sum of the column for birthdate, Birthdate column and the current date and time.

However, the age appearing in this Age column, it does not seem to look like an actual age. This is because it's an expression of time.

Duration

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

days.hours.minutes.seconds

You will discover in these values. But, from a person's point of view, they shouldn't have to look for details like the ones listed above. There are methods that could find every moment of time. By using the Duration menu, you'll notice the amount of seconds to minutes, hours and years out of it.

To calculating the age in years such as, for instance it is as simple as going the Total Years.

The duration is measured in days and then divided by 365. This gives you the value of the year.

Rounding

No one has claimed they are 53.813698630136983! they say it as 53, but with a rounding down. You can choose the Rounding option and round down in the Transform tab.

This will show you how old you are:

Then, you can purify other columns at your discretion (or you might have employed transformations from the Transform tab to stop creating new columns.) The column could be named column: Age.

Things to Know

  • Refresh: The age calculated using the method can be refreshed every time you refresh your data. And each time, it will compare your birthdate with the date and moment of the refresh. This method is pre-calculating an age. If you, however, require the calculation of an age to be carried out dynamically, employing DAX, here's how I described a way that you could use.
  • Arguments for choosing Power Query: Benefits of performing age calculations in Power Query is that the calculation is performed when you refresh your report. This is achieved by making use of a tool to make the calculation simpler and no requirement for the additional cost of doing it using DAX as a measurement of the runtime.
  • Another scenario , this cannot be used to calculate the birthdate as an age. This can be used to calculate inventories of products as well as to determine the difference between two dates and times one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc of Computer engineering. Over 20 years' experience in data analysis database programming, BI, development and programming primarily focused in Microsoft technologies. He is a Microsoft Data Platform MVP for nine years (from 2011 until now) due to his dedication in Microsoft BI. Reza is an incredibly prolific writer and is co-founder with RADACAD. Reza is also 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 a number of other books. He was also a regular member of online forums for technical issues like MSDN and Experts-Exchange as well as moderator of MSDN SQL Server forums, as well as an MCP and an MCSE and the MCITP for BI. He is the director of the New Zealand Business Intelligence users group. Also, he's the author of the highly acclaimed publication Power BI from Rookie to Rock Star, which is available for download for free and includes more than 17000 pages of data and an additional book called Power BI Pro Architecture published by Apress.
Speakers are an International speaker at 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 dream is to help you find the most efficient methods to manage data, and is a Data enthusiast.This blog post appeared in 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 reference to bookmark.

Post navigation

- Share different Visual Pages using different security groups of Power BIAge's Year Calculation that works for Leap Year in Power BI using 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 It is a converter calculates what volume of gas measured in ppm units and transforms it into quantities [mg/m 3] and the reverse. The unit ppm is employed in a variety of branches and ways. The use of ppm must be noted in fields below for input to determine how it will be converted into the proper unit. For more details on the use of ppm refer to the instruction below.In this section, there is an entry field for is the Molecular Weight you could either select from the drop-down menus or add the molecular mass of the substance. If you are unsure of the molecular value to you, then you should test our Molecular weight Calculator.When you enter your formula using either and the formula in units of [ppm] or units of [mg/m]. 3 The Value is transformed directly and is displayed at the lower left. This is how the importance of the number is calculated automatically. The significance is incre...
Read more

random number generator

Random Number Generator Utilize the generator to receive an unquestionably randomly digitally safe number. It generates random numbers that can be used when reliability of results is critical, like when shuffling a deck of cards during playing Poker and drawing number for giveaways, sweepstakes or lottery. What is the best method to pick the most random number between two numbers? You can use this random number generator for you to create a genuine random number from any two numbers. For instance, to get a random number within the range of 1 to 10 and even 10, you need to enter 1 first in the input and 10 in the next field, after which you press "Get Random Number". Our randomizer will select one of the numbers 1 to 10 random. To generate a random number between 1 and 100, use similar methods with 100, however, it falls within the second field on the randomizer. In order to simulate a roll of dice the range of numbers to be between 1-6 for a normal six-si...
Read more

BMI Calculator

BMI Calculator It's The Body Mass Index Calculator ( BMI calculator ) is an essential tool that can help you determine the body's weight. The calculator is able to ensure you're a healthy weight, this decreases the likelihood of getting weight-related ailments, like high blood pressure as well as heart issues related to type 2 diabetes and many other. What Is BMI (Body Mass Index)? BMI is an indicator measure that determines if you're overweight, overweight normal-weight or underweight for your weight. BMI evaluates your body mass to determine if your body mass falls to the healthy weight range. Along with height, BMI also depends on the age of your body and gender. It is possible to use the online BMI calculator to clarify the significance of. BMI is a measurement of an individual's leanness or corpulence, based on their size and weight. It is used to determine the mass of tissue. BMI is used to determine whether adults fall within the following cat...
Read more