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 preferred languagein several analysesin Power BI, many do not know about the function available in Power Query. In this blog I'm going to explain how easy it is to calculateAge in Power BI by using Power BI. The methodis extremely efficient in situations where the Age calculationcan be carried out on an already 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 a few of the extra columns to make it more readable;

If you want to calculate the age of each customer, all you have to do is to:

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

That's it. This can calculate an amount that is the sum of the Birthdate column, along with the current date and time.

But, the age you see under the Age column, doesn't appear to be an age. It's because it's a duration.

Duration

Duration is a unique data type found in Power Query which represents the differences of two DateTime values. Duration is a mixture of four values:

days.hours.minutes.seconds

and that's how you can interpret the data above. But from users' perspective you don't want them to search for particulars like that. There are ways you can fetch each part from the length. By selecting the Duration menu it will show that you can get the number of seconds, minutes, hours, days, and years from it.

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

Take note that the duration of the program is calculated in days , and then divided by 365, to give you the yearly value.

Rounding

At the final point, no one says that their age is 53.813698630136983! They refer to it as 53, with a rounding down. You can select Rounding and Round Down from the Transform tab for it.

This will give you the age in years:

Then you can tidy the other columns, if desired (or perhaps you've applied transformations using the Transform tab to avoid any creation of columns) And name this column; Age:

Things to Know

  • Refresh: The age calculated by this method will be refreshed at the time of refreshing your database. and each time will compare the date of birth with the date and date that the data refresh took place. This method is an earlier calculation of age. If you require the calculation of age to be performed dynamically with DAX, here I explained how you can employ.
  • How to use Power Query: Benefits of performing age calculations in Power Query is that the calculation is made during the refresh of your report. This is done by using an instrument that makes the calculation more simple, and there won't be extra overhead in calculating it using DAX as a measure runtime.
  • Additional scenarios It is not for calculating age only on the basis of birthdate. This is a good way to determine product inventory and for 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 has more than 20 years' experience in data analysis database, BI, programming, and development predominantly on Microsoft technologies. He is an official Microsoft Data Platform MVP for nine years in a row (from 2011 till now) for his dedication in Microsoft BI. Reza is a prolific author and co-founder at RADACAD. Reza is also co-founder and co-organizer of 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 has written a few books on MS SQL BI and also is writing a few more. He was also an active participant in online forums for technical issues like MSDN and Experts-Exchange and was the moderator of MSDN SQL Server forums, and holds the MCP and MCSE as well as an MCITP of BI. He is the director of the New Zealand Business Intelligence users group. He is also the creator of the book that is very well-loved 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.
The speaker 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 dream is to help users find the best data solution. He is a Data enthusiast.This article was published under Power BI, Power BI from Rookie to Rockstar, Power Query and included in 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 Age Calculation that can be used to calculate Leap Year in Power BI using Power Query -

Comments

Post a Comment

Popular posts from this blog

What Is a Calorie?

Calorie Measurement device Calorie can be an expression used to define an energy or heat, which is defined. Calorie was initially defined as calorie is initially described as the quantity of heat needed to be produced in a 1 standard atmosphere to increase the temperature of one gram of water by one degree Celsius. From 1925 onwards, the calorie was determined in terms of the joule as well as the definition of the term since 1948 being that one calorie equals approximately 4.2 Jourles. The amount of heat within the calorie is widely understood to differ according to temperature (by just one percent) which is why it was necessary to establish what temperature water's heat is to be taken to be 1 calorie. Thus"15deg calories "15deg calorie" (also called the gram-calorie, or small calorie) is determined by being the sum of heat that can increase the temperature of 1 kilogram of water between 14.5deg to 15.5deg C. It is the equivalent of 4.1855 joules. Some oth...
Read more

power-converter

Energy conversion transformation of energy generated by nature into the form that can be used by humans. Over time an array of devices and systems have been developed for this purpose. A majority of these conversion devices are fairly simple. Early windmills, like employed to convert the movement that the wind generated into electrical energy which was used later to pump water and grind grain. The other energy conversion systems are more complex, especially ones that use fossil-fuels as well as nuclear fuels to create electrical energy. These systems have a variety of steps or processes which are undergoing a series of transformations in various types of intermediate forms. The majority of energy conversion devices utilized today convert the electrical energy produced by thermal heat to electricity. The efficiency of such devices is subject to limits fundamentally, following the laws of thermodynamics as well as other theories of science. Recently, a lot of focus has been plac...
Read more

Convert Length and Distance Units Instantly

  LENGTH CONVERTER Select the length you'd like to use, and then type in the amount you'd prefer to convert to a different length measurement within the "From" field, and select the measurement you'd like to convert it to within the "To" field.   The results of the conversion will be displayed in the "To" field right after you click on any item. It's that the term"length," which you've probably heard of , is used to describe "length" as a precise  measurement  of any thing from one end to the other.   It is difficult to find precise measurements that convert kilometers into miles.   Sometimes, we need an estimate of how long it takes others to reach their destination or the time required to get there. This is why length converters are useful in the calculation process.   Measurement  Converter is a device that is able to be utilized in accordance with the needs since it can convert any length and can aid you in solvi...
Read more