Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. However, as DAX is the most used language usedin numerous calculationsin Power BI, many do not know about the function available in Power Query. In this blog article I'll explain how easy it is to calculateAge in Power BI with Power BI. The methodis extremely helpful when your age calculationcan be performed on a pre-calculated row by row basis.

Calculate Age from a date

Below is the DimCustomer table from the AdventureWorksDW table that acts as the birthdate column. I've removed the columns that aren't needed to make it easier to read.

For you to calculate your age for each consumer, all you need is to:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; start by selecting the Birthdate column.
  • go to Add Column Tab, and then click on the "From Date & Time" section, and under Date, select Age

That's it. this does calculate any difference in the Birthdate column, as well as the current date and time.

However, the age that appears in the Age column, does not really look like an age. That is because it is a Duration.

Duration

Duration is a distinct kind of data type used of data type in Power Query which represents the differences between the two DateTime values. Duration is a combination of four numbers:

days.hours.minutes.seconds

This is the way to read the above values. However, for users' perspective they shouldn't be expected to look up details like that. there are ways that you are able to get each segment that is the amount of time. If you click on the Duration menu you'll see that you can get the number of seconds to minutes, hours days, and years from it.

For calculating the age in years, for example it is easy to hit Total Year:

The duration is calculated in days , and after that divided by the number of days to give you the annual value.

Rounding

It's the truth, no one declares that their age is 53.813698630136983! They call it 53, and then round it down. You can select Rounding option and then the round down from the Transform tab for it.

This will give you the age in years:

Clean up the other columns, if desired (or it could be that you utilized transformations using the Transform tab to avoid making new columns), and call this column; Age:

Things to Know

  • Refresh The date calculated this way will get refreshed at the time of refreshing your dataset. and each time will compare dates of birth and the date and timing for the refreshing. This method is an initial calculation of the age. If you need the age calculation to be dynamically done using DAX This is the way I've explained how to make use of.
  • Why Power Query: Benefits of performing age calculations in Power Query is that the calculation takes place at the time of refreshing your report. The report is refreshed using a tool that makes calculation less complicated, and also there's no added cost of calculating it using DAX as a measure runtime.
  • Another scenario It is not meant to calculate age only by birthdate. This can be used to calculate inventory of products as well as the different between two dates or times from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has earned a BSc in Computer engineering. He holds more than 20 years' experience in data analysis data, BI, databases programming, and development predominantly on Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years in a row (from 2011 to the present) because of his dedication to Microsoft BI. Reza is an active blog writer, and the co-founder and editor of RADACAD. Reza is also co-founder as well as co-organizer of the Difinity event 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 some books on MS SQL BI and also is working on other books. He was also a regular participant in online forums for technical issues like MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP and MCSE as well as an MSCITP of BI. He is the head for the New Zealand Business Intelligence users group. He is also the author of the book that is very well-loved Power BI from Rookie to Rock Star, which is free and has more than 1700 pages of content and an additional book called Power BI Pro Architecture published by Apress.
His credentials include being 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 passion is to help users find the best data solution. He is a Data enthusiast.This entry was posted under Power BI, Power BI from Rookie to Rockstar, Power Query and is filed under 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 Power BIAge's Years Calculation that works for Leap Year in Power BI with Power Query

Comments

Post a Comment

Popular posts from this blog

Random Number Generator

In reality, the majority of random numbers used in computers is pseudo-random and are created according to a set pattern using the mathematical formula. This is fine for a variety of purposes, but it might not be random in the manner one would expect, if you're utilized to lottery or dice roll drawings. RANDOM.ORG gives genuine random numbers to any user on the Internet. The randomness comes from atmospheric noise, which for many purposes is superior to false-random number algorithms usually used in computer programs. People use RANDOM .ORG to hold drawings, lotteries and sweepstakes, to drive online games, to support applications that are scientific and also for art and music. It has been operating since 1998, and was created by Dr. Mads Haahr of the School of Computer Science and Statistics at Trinity College, Dublin in Ireland. In the present, RANDOM.ORG is operated by Randomness and Integrity Services Ltd.FREE services Games and Lotteries Lottery Quick Pick is perh...
Read more

All Fruits Name In Hindi and English

  All Fruits Name   In Hindi and English with   images   Do are your familiar with names for fruits both in Hindi as well as English ? All Fruits Name  In Hindi in Hindi and English with  images  Do are your familiar with names and names of fruits and fruits in Hindi as well as English ?   Learn about fruit names (  Phal ke Naam ) in Hindi and English through pictures.   For more information on how to name fruits, continue reading. Buddy, you've probably eaten  fruits .   I'm not sure there'll be someone who'd be shy when it comes to  fruits name .   All people over the world consume fruits with fantastic fervor.   In this article, we are going to give you information about name of all fruits. Friends, we all are aware of the advantages fruits provide for our health.   We don't even need to remind you .   All of us need to eat the most fruits that we are able to.   It is very beneficial for our hea...
Read more

Durga Chalisa Lyrics in Hindi

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