Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, since DAX is the primary language usedin numerous computationsin Power BI, many do not know about this feature in Power Query. In this article, I'll show the process of how to calculateAge in Power BI using Power. This methodis very beneficial when your estimation of the agecan be done as an earlier calculated row-by-row row basis.

Calculate Age from a date

Below you will find the DimCustomer table, which is a comprised of AdventureWorksDW table, which acts as your birth date column. I've taken out a few of additional columns in order to simplify the look through.

To calculate your the age of each customer, all you need is to:

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

That's that. This is the method you calculate an amount which is the sum of the column for Birthdate column, together with the current date and time.

However, the age appears in"Age" but, since it's under the Age column, but doesn't appear to be an actual age. It's due to the fact that it's an actual length.

Duration

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

days.hours.minutes.seconds

This is how you can take the information above into consideration. In the eyes of the user, it's not their responsibility to read the entire details of this. There are techniques that can extract every bit of information from the period. When you select"Durnancing" in the Menu, you will find that it is possible to take the number of minutes and seconds along with days, hours and years from it.

To assist in calculating the age in years like, for instance you simply press Total Year:

Note that the duration is measured in days . After that, it is then was divided by the days to give the total annual amount.

Rounding

In reality, no person declares that they are 53.813698630136983! They claim 53, which is an arbitrary number that is rounded down. It's easy to select Rounding and then round down from the Transform tab.

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

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

Things to Know

  • Refresh The age calculated in this manner is refreshed each time you're refreshing your information. Each time, it will compare dates of birth with the date and date at the time of refresh. This method is a pre-calculation of an age. If you need the calculation to be dynamically done using DAX this is the way I explained the method you could use.
  • The motive behind Power Query: Benefits of performing an age calculation with Power Query is that the calculation is made during the refresh of your report, using an instrument that makes the calculation more efficient and speedier, and there won't be extra overhead to calculate it using DAX to measure the time.
  • Alternative scenarios It is not used to calculate the age from birthdate. This can be used to calculate an inventory level age calculation and the differences 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 is more than 20 years old. experience in the area of data analysis, BI, databases, programming and development primarily in Microsoft technologies. He has been an official Microsoft Data Platform MVP for nine years in a row (from 2011 until the present) for his passion of Microsoft BI. Reza is a prolific blog writer as well as co-founder and editor of RADACAD. Reza is also co-founder 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 additional books. He also was a regular participant in online forums dealing with technical matters like MSDN and Experts-Exchange and was 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 founder of the New Zealand Business Intelligence users group. The group also is creator of the well-known guidebook Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and The 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 Users Groups. And He is a Microsoft Certified Trainer.
Reza's dream is to help users find the most effective data solution. He is an avid Data enthusiast.This post was published 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 filed under Power BI. Bookmark the permalink.

Post navigation

- Share Different Visual Pages with different security groups within Power BIAge's Years Calculation that is used to calculate Leap Year in Power BI using Power Query

Comments

Post a Comment

Popular posts from this blog

BMI Calculator

BMI Calculator Knowing BMI can help adult both women and men to better understand their general health. Use the BMI calculator listed below in order to calculate your BMI. BMI calculator here to determine the body mass index of your body by entering your height and weight. It is a BMI calculator that uses the BMI formula to determine the following formula: Body mass index (lb) (Lb) (Lb) / (Height (in))2 703. Understanding Your Body Mass Index The problem lies in the fact that you're BMI is less than 18.5: Your BMI is considered to fall into the category of overweight. Keep in mind that being overweight in your BMI calculation may result in certain health risk. Your healthcare doctor for further information about BMI calculation. The reality could be your BMI is in the range of 18.5-24.9: Your BMI is considered to be normal. The weight you are at is healthy and reduces your chance of having serious illnesses and indicates that you're near to achieving all you...
Read more

Durga Chalisa Lyrics in Hindi

  Durga Chalisa Lyrics in Hindi ( durga chalisa pdf ) नमो नमो दुर्गे सुख करनी । नमो नमो दुर्गे दुःख हरनी॥ निरंकार है ज्योति तुम्हारी। तिहूं लोक फैली उजियारी॥ शशि ललाट मुख महाविशाला। नेत्र लाल भृकुटि विकराला॥ रूप मातु को अधिक सुहावे। दरश करत जन अति सुख पावे॥ तुम संसार शक्ति लै कीना। पालन हेतु अन्न धन दीना॥ अन्नपूर्णा हुई जग पाला। तुम ही आदि सुन्दरी बाला॥ प्रलयकाल सब नाशन हारी। तुम गौरी शिवशंकर प्यारी॥ शिव योगी तुम्हरे गुण गावें। ब्रह्मा विष्णु तुम्हें नित ध्यावें॥ रूप सरस्वती को तुम धारा। दे सुबुद्धि ऋषि मुनिन उबारा॥ धरयो रूप नरसिंह को अम्बा। परगट भई फाड़कर खम्बा॥ रक्षा करि प्रह्लाद बचायो। हिरण्याक्ष को स्वर्ग पठायो॥ लक्ष्मी रूप धरो जग माहीं। श्री नारायण अंग समाहीं॥ क्षीरसिन्धु में करत विलासा। दयासिन्धु दीजै मन आसा॥ हिंगलाज में तुम्हीं भवानी। महिमा अमित न जात बखानी॥ मातंगी अरु धूमावति माता। भुवनेश्वरी बगला सुख दाता॥ श्री भैरव तारा जग तारिणी। छिन्न भाल भव दुःख निवारिणी॥ केहरि वाहन सोह भवानी। लांगुर वीर चलत अगवानी॥ कर में खप्पर ...
Read more

Age Calculation

Age Calculation in Power BI using Power Query Power Query has a simple method to calculating the age . However, since DAX is the main language used in numerous calculations in Power BI, many do not know about this feature in Power Query. In this article, I'll show how simple it is to calculate Age within Power BI with Power BI. The method is very beneficial when your estimation of your age can be calculated on an earlier calculated row-by-row row basis. Calculate Age from a date Below, you will see the DimCustomer table, which is an integral part of the AdventureWorksDW table, which acts as an entry point for the birthday column. I've taken out a few of the columns that aren't needed in order to simplify the understand. To calculate your age for each of your customers All you have to do is to: In Power BI Desktop, Click on Transform Data In the Power Query Editor window, select the first column, Birthdate. Go to the Add Column Tab to the Col...
Read more