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 primary languagein numerous functionsin Power BI, many do not realize the function available in Power Query. In this blog post I'll demonstrate how simple it is to calculateAge in Power BI with Power BI. It is a methodis extremely beneficial for situations when you need to calculate the age calculationcan be calculated on pre-calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table from AdventureWorksDW table, which is a birthdate column. I've removed a few of the columns that aren't needed to make it easier to read.

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

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; make sure to select the Birthdate column first.
  • Go to the add Column Tab. Under "From Date & Time" section, and under Date, select Age

That's it. This does calculate how much difference there is between Birthdate column, and the date and time.

The age you see under the Age column, doesn't really appear like an age. That is because it is a duration.

Duration

Duration is a specific type of data within Power Query which represents the distinction from two DateTime values. Duration is a combination of four values:

days.hours.minutes.seconds

That's the way to read the values above. For users' perspective you don't want them to search for specifics like the ones above. there are ways that you are able to get each segment from the length. using the Duration menu option, you will see the amount of seconds or minutes, hours, days, and years from it.

For calculating the age in years such as, for instance, you can simply hit Total Year:

It is important to note that the duration is measured in days and later divided by 365, to give you the annual value.

Rounding

At the final point, nobody says your age in 53.813698630136983! they say it as 53, rounded down. You can select Rounding option and then the round down from the Transform tab for it.

This will provide you with the number in years:

Clean up the other columns if you'd like (or you could have employed transformations in the Transform tab to prevent any creation of columns) Then, you can call this column: Age.

Things to Know

  • Refresh The age that is calculated by this method will be updated when refreshing your database. and each time will compare your birthdate with the date and the time of the refresh. This method is an algorithm for pre-calculating the age. If you require that the calculation be dynamically done using DAX here is how I described how to use.
  • What is the reason? Power Query: Benefits of using age calculation with Power Query is that the calculation is performed during the refresh of your report. The report is refreshed using an algorithm that makes the calculation simpler, and there won't be extra overhead of calculating it using DAX as a measure runtime.
  • Different scenarios These are not meant to calculate age only by birthdate. It can be used to calculate the age of inventory for products and also 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 holds been awarded a BSc in Computer engineering; he has more than 20 years' experience in data analysis and BI, databases, development, and programming mainly focused on Microsoft technologies. He is an official Microsoft Data Platform MVP for nine years in a row (from 2011 until today) for his dedication in Microsoft BI. Reza is a prolific blogger and co-founder of RADACAD. Reza is also co-founder as well as co-organizer of Difinity conferences and Difinity conference 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 a few more. He was also an active member of online technical forums like MSDN and Experts-Exchange, and was moderator of MSDN SQL Server forums, and holds the MCP, MCSE, and MSCITP of BI. He is the leader in the New Zealand Business Intelligence users group. Additionally, he is the writer of the highly popular book Power BI from Rookie to Rock Star, which is available for free and has more that 700 pages of content, as well as The Power BI Pro Architecture published by Apress.
It is an International Speaker in 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 main goal is helping you find the right data solution. He is a Data enthusiast.This post was filed by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and tagged 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 Age Calculation that works for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

Random Number Generator

Random Number Generator Utilize the generator to obtain a trully random and cryptographically safe number. It creates random numbers that can be used when the accuracy of results is crucial, like when shuffling decks of cards for a poker game or drawing numbers in a lottery, giveaway, or sweepstakes. How do I pick an random number from two numbers? This random number generator to select a truly random number from any two numbers. To get, for instance, a random number from 1-10 with 10, you must enter 1 first in the field and 10 in the next, and then click "Get Random Number". Our randomizer selects the number 1 to 10 at random. For generating the random number between 1 and 100, do the same however, with 100 within the 2nd field on the picker. When you wish to simulate a dice roll the range should be from 1 to 6, for a normal six-sided die. If you want to generate many unique numbers select how many you need from the drop-down menu below. For instance,...
Read more

teacher full form

  teacher full form  What does teacher refer to? In this blog you will be learning details about teacher full form, the complete form for a teacher, teacher ka full form, the complete teacher template, teacher ki full form..   The reader will be able to find the entire information in this blog.   This blog will provide everything you must be aware of about teacher full form, ...   I hope that you will enjoy this article. What is the meaning of Teacher ? Noun : A teacher is an teacher, especially in schools. The most commonly used terms for TEACHER are: - Talented Educated Adorable Charming Helpful Encouraging Responsible - Talented Efficient Able Cheerfulness Humble Enthusiastic Resourceful Top Ten Tips  to be the Best Teacher:  1 1.   When you're thinking about your explanation, "Know what the students know". Great teachers  are adept at communicating with their students in a manner that is informative and engaging.   This knowledge is ...
Read more

Age Calculator

Online Age Calculator The trend for online social media is gaining popularity throughout the world due the lack of time. and also a hectic schedule of the people. Since there is an online online age calculator operates extremely swiftly, you'll need to input your data and after putting your data in the result will appear in fractions of timing. Age calculator online is now an extremely easy calculator even having a regular or average educational background and you can also operate this calculator in the correct manner. Today because life has become more rapid than you can think about a single second before you are in India and within a minute you're working out on Mars, so it is very hard to remember your birthday. For those who can not remember their birthday, correctly this birthday to age calculator is a powerful tool that can help remember your birthday in a way that is automatic using the Birthday to age calculator . This trend to remember the birthday by u...
Read more