Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method for calculating the age. However, since DAX is the primary language usedin numerous calculationsin Power BI, many are not aware of this feature of Power Query. In this article, I'll demonstrate how simple it is calculateAge within Power BI using Power BI. It is a methodis extremely useful in situations where your estimation of the agecan be done as an earlier calculated row by row basis.

Calculate Age from a date

Below you can see the DimCustomer table, which is comprised of AdventureWorksDW table which functions as the birthdate column. I've removed several of the extra columns to make it easier to read.

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

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

That's that. This is how you calculate an amount which is the total of the Birthdate column, in addition to the current date and time.

The age, however, which appears in"under" the Age column, however, it does not appear to be the actual age. It's because it's actually a duration.

Duration

Duration is a distinct data type within Power Query which represents the variation between the two DateTime values. Duration is a combination of four numbers:

days.hours.minutes.seconds

This is how you look at the data. But, from the point of view of the user it is not their responsibility to look up the entire details of this. There are methods that could be able to get every component of the period. By selecting the Duration menu, you will see that you are able to determine the number of seconds and minutes, hours, days and years out of it.

To assist with calculating the age in years for example you simply press Total Year:

Be aware that the duration is measured in days . Then, it is multiplied by days, for the year's total.

Rounding

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

This will reveal how old you are:

Then, you can clean the other columns, if desired (or perhaps you've made use of transformations through the Transform tab to avoid having to create new columns) And name this column: Age

Things to Know

  • Refresh The age that is calculated this way will be refreshed every time you're refreshing your information. Each time, it compares dates of birth with the date and time at the time of refresh. This method is an algorithm for pre-calculating an age. If, however, you need the calculation to be dynamically done using DAX here is how I described the method you could apply.
  • The reason behind Power Query: Benefits of performing an age calculation using Power Query is that the calculation takes place during the refresh of your report. This is done using an instrument which makes the calculation easier and faster, and there is no additional overhead when it is calculated using DAX for measuring runtime.
  • Alternative scenarios It is not utilized to calculate the age of a person based on their birth date. This is a good way to determine inventory-level ages for inventory items and also 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. He has more than 20 years old. experience in the field of data analysis and databases, BI and development, mostly using Microsoft technologies. He is an official Microsoft Data Platform MVP for 9 consecutive years (from 2011 until the present) because of his enthusiasm to Microsoft BI. Reza is a prolific blog writer, and the co-founder and director of RADACAD. Reza is also co-founder as well as co-organizer for Difinity Conference. 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 writing more books. He was also a frequent participant in forums online for technical issues like MSDN and Experts-Exchange and was also the moderator of MSDN SQL Server forums, and is an MCP and MCSE and an MCITP of Business Intelligence. He is the leader in the New Zealand Business Intelligence users group. In addition, he is the creator of the highly acclaimed workbook Power BI from Rookie to Rock Star, which is completely free with over 170 pages of content. The Power BI Pro Architecture published by Apress.
It 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 passion is to help users find the best data solution. He is an avid Data enthusiast.This blog post has been published by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is filed in Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. The following entry was filed under Power BI.

Post navigation

- Share different visual pages using different security groups in Power BIAge's Years Calculation which works for Leap Year in Power BI through Power Query

Comments

Post a Comment

Popular posts from this blog

angle-converter

Image
What is an angle ? The angles that can be acute, sharp,, or narrow. An angle is an form created by two rays. Both have a common point of origin known as the vertex . It is possible to ask why angles are significant? It is possible to determine the size of a tower once it is known the length between you and it, as well as the angles between the surfaces as well as the top. By using the exact technique the possibility exists that you determine the size of the moon is, or with the appropriate tools, the radius our earth is. In addition, if you throw something, and you're trying to figure out the distance it's going to travel, you'll have to consider the angle that the object is thrown from. There are many other areas that require angles , however, for now, let's focus on basic geometry. Angles are classified by their dimensions: WHAT IS A FREQUENCY CONVERTER ? The frequency converter may also be called a the power frequency convertor. It's a device that abs...
Read more

BMI Calculator

BMI Calculator Understanding BMI will assist adult both women and men to better understand their general health. Utilize the BMI calculator beneath to calculate the body mass index of your. BMI calculator here to calculate the body mass index of your by inputting your weight and height. It is a BMI calculator uses the following BMI formula to calculate the weight: the weight (lb) (Lb) / (Height (in))2 703. Understanding Your Body Mass Index The situation is that your BMI is lower than 18.5: Your BMI is believed to fall within the category of overweight. Remember that an overweight BMI calculation could result in certain health risks. Your healthcare doctor for further information about BMI calculation. The case could be your BMI is within an area of 18.5-24.9: Your BMI is considered normal. A healthy weight reduces your chances of developing serious diseases and shows that you're in the process of achieving what you want to achieve in fitness. Your BMI is bet...
Read more

scientific calculator

Calculator Use The graphing scientific and multi-function computers are acceptable or available for certain Practice (r) tests. Unless it is explicitly stated that a calculator is allowed to be used for a particular test the calculator cannot be used. Test-takers are not permitted to share calculators. Tests That Do Not Permit Use of a Calculator In general, unless it is specifically stated that a specific type of calculator is permitted or required for a specific test, calculators are not allowed to be used for all practice test. Tests That Permit the Use of a Calculator You can use a four-function or scientific calculator to pass your Middle School: Content Knowledge (5146) test, though you're not required to use it. You are required to bring your own calculator to the test center. It is not allowed on the day of the exam. A graphing calculator isn't allowed during this test. Types of Calculators That Are Not Permitted The following items are no...
Read more