Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. But, because DAX is the primary languagein numerous computationsin Power BI, many are not aware of this function in Power Query. In this article I will demonstrate how simple it is to calculateAge in Power BI with Power BI. The methodis extremely beneficial when your estimation of the agecan be done as an earlier calculated row by row basis.

Calculate Age from a date

Below is the DimCustomer table that is part of the AdventureWorksDW table which as an age column. I've removed the columns that aren't needed to make it easier to read.

To calculate the actual age of each consumer, all you need is:

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

That's it. This will calculate how much difference there is between Birthdate column as well as the current date and time.

However, the age that you see on the Age column doesn't actually appear to be an age. That is because it is an actual duration.

Duration

Duration is a special form of data of data type in Power Query which represents the distinction between two DateTime values. Duration is a combination of four values:

days.hours.minutes.seconds

and that is the way to read the information above. From the perspective of the user they shouldn't be expected to be able to access the particulars of that. there are ways that you can retrieve each component of the time. If you click on the Duration menu you'll notice how you can extract the amount of seconds or minutes, hours, days, and years from it.

To help in calculating the age in years such as, for instance the age of a person, simply hit Total Year:

The duration is measured in days and was then divided in 365, to provide you with the annual value.

Rounding

Finally, no one says their age as 53.813698630136983! They say 53, which is rounded down. It is simple to choose Rounding and Round Down from the Transform tab.

This will provide you with the age in years:

You can then clean the other columns, if desired (or perhaps you've employed transformations in the Transform tab to prevent any creation of columns) This column can be renamed as Age: column Age.

Things to Know

  • Refresh The age calculated by this method will be refreshed at the time of refreshing your dataset. and each time will compare your birthdate with the date and the time of the refresh. This method is a pre-calculation of an age. If you, however, require the calculation to be performed dynamically with DAX here is how I described how you can make use of.
  • How to use Power Query: Benefits of performing an age calculation using Power Query is that the calculation is performed when you refresh your report. You use a tool that makes the calculation easier, and there is no additional overhead of doing it with DAX to measure runtime.
  • Another scenario It is not used to calculate the age starting with the birth date. this can be used for product inventory age and the variation between two dates and dates from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc of Computer engineering. He has more than 20 years of experience in the field of data analysis and BI, databases, developing, and programming mostly using Microsoft technologies. He has been an official Microsoft Data Platform MVP for nine years in a row (from 2011 until now) because of his dedication to Microsoft BI. Reza is an active writer and is co-founder with RADACAD. Reza is also the co-founder and co-organizer for the Difinity conferences at 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 a few more. He was also an active participant in online forums for technical issues such as MSDN and Experts-Exchange, and was the moderator of MSDN SQL Server forums, and is an MCP and MCSE as well as an the MCITP for BI. He is also the leader in the New Zealand Business Intelligence users group. In addition, he's also the creator of very popular book Power BI from Rookie to Rock Star, which is free and contains more than 700 pages of content, as well as The Power BI Pro Architecture published by Apress.
His credentials include being an International Presenter 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 information solution. He is Data enthusiast.This post was filed by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and related to Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great resource for you to bookmark.

Post navigation

Share different visual pages with Different Security Groups in Power BIAge's Calculation of Years that works for Leap Year in Power BI 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