Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, as DAX is the main language usedin numerous calculationsin Power BI, many are unaware of the function that is available in Power Query. In this article, I'll demonstrate how simple it is to calculateAge with Power BI with Power BI. It is a methodis extremely beneficial for situations where the estimation of the agecan be done as an earlier calculated row by row basis.

Calculate Age from a date

Below, you will see the DimCustomer table, which is a an integral part of the AdventureWorksDW table, which acts as your birth date column. I've removed some of additional columns for easier read.

To calculate your age for each customer all you have to do is:

  • In Power BI Desktop, Click on Transform Data
  • In the PowerQuery Editor window, choose the first column for the Birthdate column.
  • Go to the Add Column Tab, and then select"Add Column Tab" and then on "From Date & Time" section. Under Date, select the age range.

That's that. this is the method you calculate an amount which is the total of the Birthdate column, in addition to the date and time of the present.

However, the date, which is displayed in"under" the Age column, doesn't appear to be an actual age. It's because it's not an actual duration.

Duration

Duration is a particular kind of data within Power Query which represents the difference between the two DateTime values. duration is a combination of four values:

days.hours.minutes.seconds

This is how you determine the meaning of the data. In the eyes of the user it is not their responsibility to go and read the details of this. There are methods that could extract every bit of information from the period. By selecting the Duration menu, you will discover that you can to extract the number of minutes and seconds along with days, hours and years from it.

For assistance in calculating the age in years like, for instance the age of a person, simply click Total Year:

Take note that the duration of the time is calculated in days . Then, it is divided by the number of days, in order to get the annual sum.

Rounding

No person declares that they are 53.813698630136983! They use the word 53 which is the number rounded down. It is easy to select Rounding and round down on the Transform tab.

This will reveal that you're old enough to be

You can then clean the other columns, if you wish (or perhaps you've made use of transformations with the Transform tab, avoiding having the task of creating new columns) And name this column: Age

Things to Know

  • Refresh The age that is calculated this way is updated every time you are refreshing your data. And each time it will be comparing the birth date to the date and date at the time of refresh. This method is pre-calculating an age. If, however, you require the calculation be performed dynamically using DAX, this is the way I explained the method you could use.
  • The rationale behind Power Query: Benefits of performing an age calculation with Power Query is that the calculation is made during the refresh of the report. The report is refreshed using an instrument that makes the calculation much quicker and easier, and there is no additional overhead to calculate it using DAX to gauge the time.
  • Alternative scenarios It cannot be used to calculate the date of birth. This could be used to calculate inventory-level ages for inventory items and also to determine the difference between two dates and times from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc on Computer engineering. He has more than 20 years old. experience in the field of data analysis and databases, BI, programming and development, mostly using Microsoft technologies. He has been a Microsoft Data Platform MVP for 9 consecutive years (from 2011 until today) because of his enthusiasm of Microsoft BI. Reza is an avid blogger, and the co-founder and editor of RADACAD. Reza is also the co-founder of and co-organizer of the Difinity Conference located in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He also wrote a few books about MS SQL BI and also is writing more books. He also was a regular participant in forums online for technical issues such as MSDN and Experts-Exchange and was also the moderator of the MSDN SQL Server forums, and holds an MCP and MCSE and an MCITP of Business Intelligence. He is the lead in the New Zealand Business Intelligence users group. The group also is writer of the highly acclaimed Book Power BI from Rookie to Rock Star, which is available for free and includes more than 1800 pages of material and It is also the author of the Power BI Pro Architecture published by Apress.
This speaker has been an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL Users Groups. And He is a Microsoft Certified Trainer.
Reza's dream is to help users to find the ideal data solution. He is an avid Data enthusiast.This piece was written by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is filed within Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

- Share different visual pages with different Security Groups within Power BIAge's Year Calculation which is applicable to Leap Year in Power BI using Power Query

Comments

Post a Comment

Popular posts from this blog

Scientific Calculator

What Is A Scientific Calculator Digital calculators were around for centuries, and they've gotten progressively better. But the first calculator that was scientifically designed and made available to the general public just a few decades ago, in 1968. These devices are now a common sight in classrooms around the world. But what do they actually mean? What is more, how do they different from basic calculators? I've outlined the tool's history features, functions, uses and the differences between regular and scientific calculators in this piece to help you begin using the program. What is a Scientific Calculator ? Scientific calculators can be described as digital calculators packed with functions to help solve engineering, science, and mathematics-related problems. They allow users to solve trigonometry-related problems by using the trigonometric, inverse as well as hyperbolic, functions. Users can also work with degrees, radians and grads by simply ...
Read more

BMI Calculator

BMI Calculator Knowing BMI can help women and men in understanding their general health. Make use of BMI calculator beneath to determine your BMI. BMI calculator above to determine the body mass index of your by inputting your weight and height. This BMI calculator that uses the BMI formula to calculate the body mass index: The weight of a person is (lb) (Lb) (Lb) / (Height (in))2 703. Understanding Your Body Mass Index The problem is that your BMI is below 18.5: Your BMI is believed to be within the category of overweight. Be aware that being overweight in your BMI calculation could cause some health risks. You should consult your healthcare physician for more information regarding BMI calculation. The reality could be your BMI is in the range of 18.5-24.9: Your BMI is considered normal. The healthy weight will reduce your chance of having serious illness and means that you're in the process of achieving all your goals for fitness. You're BMI is between ...
Read more