Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method for calculating the age. But, because DAX is the main language usedin numerous computationsin Power BI, many are unaware of this feature of Power Query. In this article, I'll describe the process of how to calculateAge within Power BI with Power BI. It is a methodis extremely beneficial in situations where your estimation of agecan be calculated using an earlier calculated row by row basis.

Calculate Age from a date

Below you will find the DimCustomer table, which is comprised of AdventureWorksDW table, which acts as"the birthday date column. I've taken out a few of the columns that aren't needed in order to simplify the understand.

In order to calculate your age for each customer the only thing you need to do is:

  • In Power BI Desktop, Click on Transform Data
  • In the PowerQuery Editor window; pick the first column for the Birthdate column.
  • go to the add Column Tab click on Column Tab, and then select the "From Date & Time" section, and under Date, select Age

That's that. this is how you calculate the amount which is the total of the column for Birthdate column, as well as the date and time of the present.

However, the age appears under"age" in the Age column, however, it does not appear to be a true age. It's because it's actually a length.

Duration

Duration is a particular data type within Power Query which represents the difference of the two DateTime values. Duration is a mix of four numbers:

days.hours.minutes.seconds

This is how you should interpret the data above. But, from the point of view of the user it is not expected of them to research the full details of this. There are ways to be able to get every component of the period. By choosing from the menu of Duration, you will see that you are able to determine the number of seconds and minutes, hours, days and years from it.

To assist with calculating the age in years such as, for instance it is easy to hit the Total Year:

Be aware that the duration is calculated in days . Then, it's subtracted by the amount of days to give the total annual amount.

Rounding

There is no truth in it, and no one says they're 53.813698630136983! They use the word 53 which is round down. It's easy to choose Rounding and round down from the Transform tab.

This will let you know what your old age is in terms of years.

Then, you can clean the other columns, if you wish (or maybe you've made use of transformations using the Transform tab to avoid having you create new columns) You can name this column"Age"

Things to Know

  • Refresh The age that is calculated this way will be updated each time you are refreshing your data. and each time will compare the birthdate to the date and date at the time of refresh. This method is an algorithm for pre-calculating an age. If, however, you require the calculation be performed dynamically by DAX this is the way I explained the method you could employ.
  • The motivation for Power Query: Benefits of performing an age calculation with Power Query is that the calculation is done while you refresh your report. This is done using an instrument that makes the calculation much quicker and easier, and there's no extra cost when it is calculated using DAX to gauge runtime.
  • Alternative scenarios It cannot be utilized to calculate the date of birth. This can be used to calculate an inventory level age calculation and for the variations 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 has a BSc in Computer engineering. He has more than 20 years old. experience in the area of data analysis, databases, BI and development primarily in Microsoft technologies. He is an official Microsoft Data Platform MVP for 9 consecutive years (from 2011 until now) due to his passion with Microsoft BI. Reza is a prolific blogger, and the co-founder and the editor for RADACAD. Reza is also 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 wrote several books on MS SQL BI and also is working on more books. He was also a frequent forum participant on online forums for technical issues such as MSDN and Experts-Exchange and was also the moderator of the MSDN SQL Server forums, and is an MCP and MCSE as well as an MCITP of Business Intelligence. He is the head of the New Zealand Business Intelligence users group. Also, he's the writer of the highly acclaimed guidebook Power BI from Rookie to Rock Star, which is free and has more than 1800 pages of material and It is also the author of the Power BI Pro Architecture published by Apress.
The speaker is 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 love is helping users to find the ideal data solution. He is a Data enthusiast.This post was published by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is categorized within Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

- Share different visual pages with different Security Groups within Power BIAge's Years Calculation that works for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

BMI Calculator

BMI Calculator Understanding BMI can help adults of all ages to understand their overall health. Use the BMI calculator listed below in order to calculate your BMI. BMI calculator is below. It will determine the BMI of you by entering your height and weight. It is a BMI calculator employs the following BMI formula to determine the following: Body mass index (lb) (Lb) = (Height (in))2 703. Understanding Your Body Mass Index The problem would be that your BMI is below 18.5: Your BMI is believed to fall into the category of being underweight. Remember that an overweight BMI calculation can lead to some health-related risk. Your healthcare doctor for more details on BMI calculation. The reason can be that you're BMI is within the range of 18.5-24.9: Your BMI is considered normal. The weight you are at is healthy and reduces the chance of developing serious ailments and indicate that you're in the process of achieving all your goals for fitness. It is possible...
Read more

Scientific Calculator

What Is A Scientific Calculator Electronic calculators have been around for a long time and become more and more sophisticated. But the first calculator with a scientific design was created and released to the public within the last decade, back in the year 1968. These devices can be seen in classrooms across the world, but what are they exactly? What is more, how do they different from simple calculators? I've covered the history features, functions, uses and differences between standard and scientific calculators in this blog post to get you started using the tool. What is a Scientific Calculator ? Scientific calculators are digital calculators filled with functions that can help solve engineering, science, and maths problems. These calculators permit users to solve trigonometry problems using the built-in trigonometric, inverted, and hyperbolic formulas. Users can also use degrees, radians and grads by simply switching modes. Furthermore, mathematical co...
Read more

Calorie Calculator

Which are more significant? calories and kilocalories ? "Current "calorie" that we use in our food is actually Kilocalorie. Kilocalorie is one (1) Kilocalorie is equivalent to one (1) Calorie (uppercase C). Kilocalorie is the quantity of heat required to increase the temperature of a kilogram of water by one degree Celsius. How can what is caloric value of food calculated? It is possible to think that it's due to the study of how they lead to weight gain, but it's not the case. It's not the case. Avery good question to be asked as the majority of people are unaware of the significance of calories . First , a calorie is not a thing that is able fill or be empty. It isn't possible to put calories in a bottle. It isn't possible to put calories into a bottle. calorie is an unit of energy measurement. It is that amount needed to increase temperatures of an mL, (which equals one Gram), or water one degree Celsius. If you're a stickler fo...
Read more