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 several calculationin Power BI, many do not know about this function in Power Query. In this blog post I will explain how easy it is to calculateAge in Power BI using Power BI. The methodis extremely beneficial in situations where the computation of an agecan be carried out on an earlier calculated row by row basis.

Calculate Age from a date

This is the DimCustomer table from the AdventureWorksDW table which as a birthdate column. I've removed some the columns that aren't needed to make it more readable;

For you to calculate the age of every buyer, you need is:

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

That's all there is to it. this calculates the calculate an amount that is the sum of the Birthdate column, as well as the current date and time.

However, the age that appears within the Age column, does not really appear like an age. That is because it is an actual duration.

Duration

Duration is a distinct form of data that is used in Power Query which represents the difference in two DateTime values. Duration is the combination of four values:

days.hours.minutes.seconds

This is how you read the above values. However, for an individual's perspective you don't want them to look up information like this. There are methods that can get each portion that is the amount of time. When you select the Duration menu you'll notice that you can extract the amount of seconds or minutes, hours, days and years out of it.

To help in calculating the age in years such as, for instance it is easy to select Total Years.

Take note that the duration of the program is calculated in days and afterwards divided into 365 to provide you with the annual value.

Rounding

Finally, no one says they are 53.813698630136983! They refer to it as 53, and then round it down. It is easy to select Rounding and Round Down from the Transform tab.

This will give you the age in years:

It is then possible to clean other columns if you'd like (or perhaps you've made use of transformations on the Transform tab to prevent the creation of new columns) This column can be renamed as Age: column; Age:

Things to Know

  • Refresh The age calculated in this manner will be updated every time you are refreshing your database. and each time will compare the birthdate with the date and the time of the refresh. This method is a pre-calculation of the age. If you require the calculation to be dynamically done using DAX, here I explained a way that you can use.
  • The reason behind Power Query: Benefits of performing age calculations in Power Query is that the calculation is carried out during the refresh of your report, using a tool that makes the calculation easier, and there's no added cost of doing it with DAX to measure runtime.
  • Another scenario These are not for calculating age only starting with the birth date. This could be used for the age of inventory for products and also the different between two dates or dates from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds the BSc from Computer engineering. He holds more than 20 years' experience in the field of data analysis data, BI, databases developing, and programming mostly on Microsoft technologies. He has been a Microsoft Data Platform MVP for nine consecutive years (from 2011 until now) for his commitment to Microsoft BI. Reza is an active author and co-founder at RADACAD. Reza is also co-founder as well as co-organizer of the Difinity Conference which is held 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 has written a few books on MS SQL BI and also is writing some others, He was also a regular member on online technical forums such as MSDN and Experts-Exchange and was also the moderator for MSDN SQL Server forums, and is an MCP, MCSE, and the MCITP for BI. He is the founder of the New Zealand Business Intelligence users group. He is also the creator of the well-known book Power BI from Rookie to Rock Star, which is free with more than 170 pages of content as well as it is a part of the Power BI Pro Architecture published by Apress.
It is 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 Data enthusiast.This entry was posted within 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. Bookmark the permalink.

Post navigation

Share different visual pages with different security groups within Power BIAge in Years Calculation that can be used to calculate Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

Random Number Generator

reality, most random numbers used in computer programs is pseudo-random and are created with a predetermined method through an equation. This is good for many purposes, but it may not be random in the manner you'd expect it to be if you're used to lottery and dice roll drawings. RANDOM.ORG is a website that provides genuine random numbers to any user on the Internet. The randomness originates from atmospheric noise, which for various reasons is superior to the random number algorithms typically used in computer applications. People use RANDOM .ORG for holding drawings, lotteries and sweepstakes. They also drive online games, to support scientific applications and for art and music. The website has been operational since 1998, and was designed by Dr Mads Haahr of the School of Computer Science and Statistics at Trinity College, Dublin in Ireland. Today, RANDOM.ORG is operated by Randomness and Integrity Services Ltd.FREE services Games and Lotteries Lottery Quick Pi...
Read more

Convert Length and Distance Units Instantly

  LENGTH CONVERTER Select the length that you'd prefer to use, and then enter the amount that you'd like to transform into another size into the "From" field, and select the unit that you'd like to convert it into in the "To" field.   The conversion result is visible in the "To" field right after you click on any unit. It's the term"length," which you've likely heard of , is defined by "length" as a precise  measurement  of anything from one end to the next.   It's not easy to find exact measurements for converting miles to kilometers.   Sometimes, we need an estimate of how long it will take for others to travel there or of how long it will take to travel there , and that's the reason length converters can be helpful in calculations.   Measurement  Converter is a tool which can be utilized in line with the requirements as it is able to convert to any type of length.   It assists you in solving problems relat...
Read more

All Fruits Name In Hindi and English

  All Fruits Name In Hindi and English with   pictures   Are you aware of the names for fruits which are both Hindi and English ? All Fruits Name  in Hindi as well as English including  photographs  Are aware of the name for fruits that are recognized in Hindi as well as English ?   Find out about the names of fruits (  Phal ke Naam ) in Hindi as well as English by looking at images.   For more details about what names are connected to fruits take a look. Buddy, you've surely eaten  fruits .   I'm not sure if there's one who's reluctant to use having a  fruits name .   Everybody who lives across the globe eats fruits with a great enthusiasm.   The following article we're going give you information about the names of all fruits. We all know the benefits fruits provide to our health.   It's not even necessary to remind ourselves .   Everyone should eat as many fruits that we are in a position to.   It is ...
Read more