Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. But, since DAX is the most popular language usedin numerous computationsin Power BI, many do not know about this feature in Power Query. In this blog post I will demonstrate how simple it is to calculateAge in Power BI with Power BI. The methodis extremely useful in situations where you need to calculate the calculations of agecan be performed on an already calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table that is part of the AdventureWorksDW table, which is the birthdate column. I've removed some of the columns that aren't needed to make it more readable;

In order to calculate how old every client, the only thing you have to do is to:

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

That's all there is to it. This is how you calculate what's the ratio between Birthdate column, as well as the current date and time.

But, the age appears on the Age column doesn't actually appear to be an age. This is because it's an actual duration.

Duration

Duration is a particular type of data that is used in Power Query which represents the differences in two DateTime values. Duration is a mixture of four numbers:

days.hours.minutes.seconds

This is the way to read the above values. But from the perspective of the user it is not expected of them to read specifics like this. There are methods that can get each portion of the time. By selecting the Duration menu you'll see the amount of seconds, minutes, hours days and years from it.

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

It is important to note that the duration is measured in days and after that divided by the number of days, to provide you with the annual value.

Rounding

At the final point, nobody declares that their age is 53.813698630136983! They say 53, which is rounded down. It is easy to select Rounding and then round down option from the Transform tab for it.

This will provide you with the age in years:

Then, you can clean the other columns, if you wish (or it could be that you utilized transformations in the Transform tab to prevent creating new columns) This column can be renamed as Age: column Age.

Things to Know

  • Refresh The age calculated in this manner will be updated when refreshing your data. Each time, it will match the date of birth with the date and the time that the data refresh took place. This method is an algorithm for pre-calculating the age. If, however, you require the calculation of age to be performed dynamically with DAX Here I have explained the method you could make use of.
  • What is the reason? Power Query: Benefits of using age calculation with Power Query is that the calculation is performed during the refresh of your report. It is done using an application that makes the calculation easier, and there is no additional overhead in calculating it using DAX as a measure of runtime.
  • Additional scenarios This is not a method to calculate age just by birthdate. This can be used to calculate the age of inventory for products and also 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 holds an BSc of Computer engineering. He is more than twenty years of experience in the field of data analysis and BI, databases, programmingand development primarily using Microsoft technologies. He has been an official Microsoft Data Platform MVP for nine years in a row (from 2011 until now) for his commitment to Microsoft BI. Reza is a prolific writer and is co-founder with RADACAD. Reza is also the co-founder and co-organizer of the Difinity Conference located 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 working on other books. He was also a regular participant on technical forums online like MSDN and Experts-Exchange and was the moderator for MSDN SQL Server forums, and holds the MCP and MCSE as well as an the MCITP for BI. He is the head for the New Zealand Business Intelligence users group. Additionally, he is the writer of the highly acclaimed book Power BI from Rookie to Rock Star, which is free and contains more than 17000 pages of information and Power BI Pro Architecture, which is 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 goal is to help you discover the most effective data solution. He is a Data enthusiast.This post was filed on Power BI, Power BI from Rookie to Rockstar, Power Query and was tagged with 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 using different security groups in Power BIAge's Year Calculation that is used 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? The random number generator (RNG) is an abstract mathematical concept, either mechanical or as a device, that is designed to produce random numbers. random number of numbers that don't display distinct patterns in their appearance or generation thus the term 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 form of randomness devices that have been around since ancient times such as dice, shuffled cards flipping coins and making straws. In modern computing, random number generators are developed using programming which is based on deterministic calculation, but this is not really thought to be true random because ...
Read more

What Is a Calorie?

Calorie Measurement device Calorie Calorie is a term used to describe a unit of energy or heat, which is defined. Calorie was originally defined as calorie it was originally described as the quantity of heat needed to be produced under a pressure of one standard atmosphere to raise the temperature of 1 Gram of water one degree Celsius. From 1925 onwards, the calorie was measured by joules that was then defined as a term has changed since 1948, which is that one calorie corresponds to approximately 4.2 Jourles. Because the quantity of heat contained in the calorie is well-known to differ at different temperatures (by 1-percent) that is why it is essential to define what temperature heat of water is to take as one calorie. Thus"15deg calories "15deg calorie" (also called the gram-calorie, or small calorie) is determined as being the total amount of heat needed to raise the temperature of 1 grams of water, from 14.5deg to 15.5deg C. It is the equivalent of 4.1855...
Read more

Online Age Calculator

5 Best Scientific Calculators For Students The use of a scientific calculator is necessary when you move beyond basic math classes. If you're doing college or high school math courses, it's almost an absolute requirement! Fortunately this scientific calculator I'm giving you in this review (the Casio FX-991EX) is both high-quality and really affordable. Here's my list of the 7 best mathematical calculators for science: Casio FX-115ES PLUS Texas Instruments TI-36X Pro Sharp EL-W516TBSL 16-Digit Advanced Texas Instruments TI-30XS MultiView HP 35s Scientific Calculator Helect 2-Line Engineering Scientific Calculator Casio FX-991EX 1. Casio FX-991EX ClassWiz I quickly fell in love with this calculator. For me , who is a visual learner , this was the perfect. It's got a vastly improved the screen's resolution compared to its predecessor (4 timed better than the ES model) which enabled Casio to ...
Read more