Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way of calculating the age. But, since DAX is the popular language usedin numerous functionsin Power BI, many do not know about this function in Power Query. In this blog post I will describe how simple it is to calculateAge in Power BI using Power BI. The methodis extremely useful in situations where the estimation of the agecan be carried out on an already calculated row-by-row basis.

Calculate Age from a date

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

In order to calculate the age of each customer, all you have to do 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, and then click on the "From Date & Time" section, and then under Date, select the age range.

That's it. this calculates the calculate what's the ratio between Birthdate column, and the current date and time.

But, the age you see when you look at the Age column, doesn't appear like an age. This is because it's a Duration.

Duration

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

days.hours.minutes.seconds

and that's how you can read the above values. However, from an individual's perspective they shouldn't be expected to read specifics like this. there are ways that you can fetch each part in the period. Utilizing the Duration menu, you will see that you can extract the number of seconds, minutes, hours days, and years from it.

To use the method of calculating the age in years for instance, you can simply click on Total Years:

Note that the duration is calculated in days . It is afterwards divided into 365 to yield the yearly amount.

Rounding

In the end, nobody is claiming they are 53.813698630136983! They refer to it as 53, and then round it down. You can select Rounding option and then round down on the Transform tab.

This will provide you with the number in years:

Clean up the other columns, if you wish (or the possibility is that you used transformations on the Transform tab to avoid making new columns), and call this column: Age.

Things to Know

  • Refresh: The age calculated this way will be updated at the time of refreshing your data. Every time, the system will be able to compare the date of birth with the date and date that the data refresh took place. This method is a pre-calculation of the age. If you require the calculation of age to be dynamically done using DAX here is how I described an approach you can use.
  • Why Power Query: Benefits of using age calculation with Power Query is that the calculation is performed at the time of refreshing your report. You use an instrument that makes the calculation more simple, and there's no need for the extra expense of doing it with DAX as a measure of runtime.
  • Other scenarios This is not meant to calculate age only starting with the birth date. This can be used to calculate product inventory and for 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 of Computer engineering. He holds more than 20 years' experience in data analysis database, BI, programming, and development mostly in Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years in a row (from 2011 till now) for his dedication in Microsoft BI. Reza is a prolific journalist and co-founder for RADACAD. Reza is also co-founder as well as co-organizer for the Difinity conference 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 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 as well as the moderator for MSDN SQL Server forums, and is an MCP and MCSE as well as an MCITP in BI. He is the head of the New Zealand Business Intelligence users group. Also, he's the author of the highly popular book Power BI from Rookie to Rock Star, which is available for free and has more that 1800 pages of material and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
The speaker is an International Speaker in 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 goal is to help you find the best data solution, he is Data enthusiast.This post was filed by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. Bookmark the permalink.

Post navigation

Share different visual pages using different security groups in Power BIAge's Year Calculation that is used for Leap Year in Power BI with Power Query

Comments

Post a Comment

Popular posts from this blog

All Vegetables Name In English

  All Vegetables Name   In English-Hindi   Vegetable Name   (sbjiyo ke naam) All Vegetables Name  In English-Hindi  Vegetable Name  (sbjiyo ke naam).   Friends We all know that everyone eats vegetables.   It's impossible to find anyone who doesn't.   Vegetables are a major ingredient in many dishes and are very beneficial for our health. There is a wide variety of  vegetables name  that are grown and consumed all over the world.   We're familiar with the names for  several  kinds of these veggies, but we're not aware of names for the various varieties.   This is due to the fact that certain kinds of vegetables can be only found in certain areas or countries, while others are available everywhere in the world. Students in the 1st grade, or in the second or the 2nd grade, are  required  to know the names of vegetables from the very beginning.   This will assist them in learning the fundamentals ...
Read more

Birds Name in english

  Birds Name in english Birds Name in english  and Hindi with  Birds Name  in Sanskrit Birds Scientific Name Here you have the opportunity to learn about the bird's name as well as names in Hindi and English.   To find out what the birds' name is as well as their appearance, continue reading. Are you a big fan of birds?   Like birds, guys?   I'm sure it's because of the fact that you  like  birds that are a lot as I do.   There's not a single person on the planet who doesn't be a bird lover because they're gorgeous and adorable, draw anyone's attention to them. Birds also play a significant role throughout the earth.   It doesn't matter if  it's an outdoor garden, a home on the roof or the open air, birds will surely be seen in the area.   The twitter of these birds are flying through the air, soaring through the gardens and roofs, and producing beautiful sound.   It's truly relaxing and enjoyable feeling.   If t...
Read more

What Is a Calorie?

Food restriction and time Do it work? A new study asks whether eating a restricted diet can have positive health benefits. AleksandarNakic/Getty Images The outcomes of animal studies have revealed significant metabolic benefits from restricted feeding. Early human experiments suggest these results could be of benefit to humans. A recent study takes a look at the evidence that has been gathered from both human and animal studies. The authors recommend that more research is required to understand the ways by the way that restricted eating habits could be beneficial and viable for humans. Researchers have reviewed the evidence to support the health benefits of eating time-restricted -that is a type of intermittent fasting which requires that people consume food only within the same duration each day. The review, included in the publication of the Endocrine Society, Endocrine Reviews researchers looked into animal resear...
Read more