Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way that is a simple method of calculating the age. However, as DAX is the most popular languagein several calculationsin Power BI, many do not realize the function available in Power Query. In this article I'm going to explain how easy it is to calculateAge in Power BI by using Power BI. This methodis extremely efficient when your computation of an agecan be done on a pre-calculated row and row basis.

Calculate Age from a date

This is the DimCustomer table in the AdventureWorksDW table that acts as an age column. I've removed a few of the columns that aren't needed to make it easier to read.

For you to calculate the age of each person who is a customer, all that you need is:

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

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

However, the age you see under the Age column, does not really look like an age. It's because it's an actual duration.

Duration

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

days.hours.minutes.seconds

And that's how you can interpret the above values. However, from users' perspective you don't want them to look up details like that. there are ways that you can get each portion that is the amount of time. If you click on the Duration menu, you will see that you can extract the number of seconds to minutes, hours days and years out of it.

To assist in calculating the age in years for instance you just need to go to Total Years.

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

Rounding

Finally, nobody declares they are 53.813698630136983! they refer to it as 53, with a rounding down. You can easily choose the Rounding and round down from the Transform tab for it.

This will give you the age in years:

It is then possible to clean other columns if you'd like (or there is a chance that you have used transformations in the Transform tab to prevent making new columns) Then, you can call this column Age.

Things to Know

  • Refresh The date calculated this way will get refreshed at the time of refreshing your dataset. Each time, it will match your birthdate with the date and the time of the refresh. This method is a pre-calculation of an age. If you require that the calculation be made dynamically using DAX This is the way I've explained how to make use of.
  • Why Power Query: Benefits of performing an age calculation using Power Query is that the calculation is performed during the refresh of your report. It is done using a tool that makes the calculation much easier and faster, and there won't be extra overhead in calculating it using DAX as a measure runtime.
  • Additional scenarios The Other scenarios are not a method to calculate age just on the basis of birthdate. This can be used to calculate stock-level age for inventory items and the differences between two dates and times from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has been awarded a BSc on Computer engineering. He enjoys more than 20 of experience in data analysis databases, BI, development, and programming mainly using Microsoft technologies. He has been an official Microsoft Data Platform MVP for nine consecutive years (from 2011 until today) for his dedication in Microsoft BI. Reza is a prolific writer and is co-founder with RADACAD. Reza is also co-founder and co-organizer of the Difinity event 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 some others, He was also an active member on online technical forums such as 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 director in the New Zealand Business Intelligence users group. The group is also author of the book that is very well-loved 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.
A.J. 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 to help you find the ideal data solution. He's a Data enthusiast.This entry was posted 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 by using Different Security Groups in Power BIAge's Years Calculation which works for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

Random Number Generators

What Does Random Number Generator (RNG) Mean? In other words, a random number generator (RNG) is a mathematical construct, either in the form of a computer or a hardware device, designed to produce an random number of numbers that will not exhibit any discernible patterns in their appearance or their generation, hence the word random . It is often in the form of a function or blocks of code used in software applications such as games where an element of chance is required.https://8b070a9da3e7375cd17113bc2dcafe15.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.htmlAdvertisement Techopedia Explains Random Number Generator (RNG) Random number generators are only the latest application of randomness tools that have been used from the beginning of time, such as dice, shuffled or shuffled cards, flipping coins, and even drawing straws. In modern computing, random number generators are developed using programming using deterministic computation but this is not rea...
Read more

Random Number Generators

Random number generation Random number generation is a process by which, often using the random number generator (RNG), a sequence of numbers or symbols that can't be reliably forecasted better than random chance is created. This means that the resultant sequence may contain patterns that are discernible in hindsight but unpredictable to foresight. It is true that random number generators can be hardware random-number generators (HRNGS) that generate random numbers, wherein each generation is determined by the present value of a physical environment's characteristic that changes constantly in a manner that's virtually impossible to describe. This would be in contrast to so-called " random number generations" done by pseudorandom number generators (PRNGs) that generate numbers that only look random but are in fact pre-determined--these generations can be reproduced simply by knowing the state of the PRNG. Many applications of randomness have led to...
Read more

Random Number Generator

Random Number Generator Use this generator to obtain a trully random and cryptographically secure number. It creates random numbers that can be used when the accuracy of results is critical, such as when shuffling a deck of cards for a game of Poker or drawing numbers in an auction, lottery, or sweepstakes. How do you choose how to pick a random number from two numbers? You can utilize this random number generator to pick a completely random number between two numbers. For instance, to get an random number of 1 to 10 (including 10, you must enter 1 first in the input and 10 in the next, then click "Get Random Number". The randomizer will select the number 1 to 10 at random. To create the random number between 1 and 100, do the same but place 100 within the 2nd field on the picker. To simulate a dice roll , the range should be 1-6 for a typical six-sided dice. If you want to create an additional unique number, you need to select the number of numbers you...
Read more