Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method for calculating the age. But, since DAX is the preferred language usedin several functionsin Power BI, many are unaware of the function available in Power Query. In this blog post I'm going to explain how easy it is to calculateAge in Power BI using PowerBI. This methodis extremely efficient when the estimation of the agecan be done as a pre-calculated row and row basis.

Calculate Age from a date

The table below is DimCustomer table that is part of the AdventureWorksDW table that acts as a birthdate column. I have removes some of the columns that aren't needed to make it easier to read;

To calculate the age of each client, the only thing you need is:

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

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

However, the age that you see when you look at the Age column, doesn't look like an age. This is because it's an actual duration.

Duration

Duration is a unique kind of data type used within Power Query which represents the distinction of two DateTime values. Duration is the combination of four values:

days.hours.minutes.seconds

That's how you read the values above. From the perspective of the user it is not expected of them to go and read the particulars of the ones above. there are ways that you can fetch each part from the length. Utilizing the Duration menu it will show the number of seconds and minutes, hours, days, and years from it.

To help in calculating the age in years for instance, you can simply go to Total Years.

It is important to note that the duration is calculated in days . It is was then divided in 365, to calculate the value for the year.

Rounding

In the end, no one says their age as 53.813698630136983! They refer to it as 53, then rounded down. You can select Rounding and round down from the Transform tab for it.

This will provide you with the age in years:

Then you can tidy the other columns if you want (or perhaps you've applied transformations using the Transform tab to avoid the creation of new columns) This column can be renamed as Age: column; Age:

Things to Know

  • Refresh The age that is calculated by this method will be refreshed at the time of refreshing your database. And each time, it will compare the birthdate to the date and time for the refreshing. This method is an initial calculation of the age. If you, however, require the calculation to be dynamically done using DAX here is how I described a way that you can use.
  • The reason behind Power Query: Benefits of using age calculation with Power Query is that the calculation takes place when you refresh your report. It is done using an algorithm that makes the calculation easier, and there won't be extra overhead to calculate it using DAX as a measure of runtime.
  • Another scenario It is not used to calculate the age by birthdate. It can be used to calculate 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 has earned a BSc on Computer engineering; he have more that 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 years in a row (from 2011 until now) due to his dedication to Microsoft BI. Reza is a prolific blogger and co-founder of RADACAD. Reza is also the co-founder and co-organizer of Difinity event 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 wrote some 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 is an MCP, MCSE, and MCITP of BI. He is the founder of 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 17000 pages of information and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
His credentials include being 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 love is to help you find the best solutions for data, and he's a Data enthusiast.This entry was posted under Power BI, Power BI from Rookie to Rockstar, Power Query and related to 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 Calculation of Years that can be used to calculate Leap Year in Power BI with Power Query

Comments

Post a Comment

Popular posts from this blog

What Is a Calorie?

Calorie Measurement unit Calorie It is a term used to describe a measure of energy or heat, which is defined. Calorie was first defined in terms of calorie it was initially described as the quantity of heat that must be generated in a 1 standard atmosphere to increase the temperature of one gram of water by one degree Celsius. Since 1925 this calorie was calculated in terms of the joule and the definition of the term has changed since 1948, which is that one calorie is equivalent to around 4.2 Jourles. Since the amount of heat within the calorie is widely recognized to vary according to temperature (by 1-percent) and therefore it has been necessary to define what temperature water's heat is to be taken to equal 1 calorie. Thus"15deg calories "15deg calorie" (also called the gram-calorie, or small calorie) is determined by being the total amount of heat needed to increase the temperature of 1 gram of liquid from 14.5deg up to 15.5deg C. It is equivalent to ...
Read more

Random Number Generator

Random Number Generator Utilize the generator to obtain an absolutely random as well as a cryptographically safe number. It generates random numbers that can be used where unbiased results are critical, such as in shuffling the deck of cards for a poker game or drawing numbers in giveaways, lottery or sweepstakes. What is the best way to choose what is a random number between two numbers? You can use this random number generator for you to choose a truly random number between any two numbers. For instance, to generate a random number from 1-10 and 10, you must enter 1 first in the field and 10 in the secondfield, then press "Get Random Number". Our randomizer will pick one number between 1 and 10 random. For generating a random number between 1 and 100, do the same but place 100 in the second field of the picker. If you want to simulate a dice roll the range of numbers should be from 1 to 6, for a standard six-sided dice. If you want to create more tha...
Read more

Convert to JPG

  Convert to JPG This is the most effective JPG converter provided by Img2Go.   It lets you convert multiple files to JPEG. The video and the  footage  can be uploaded onto the storage devices' hard disk, cloud storage or via clicking"upload" to choose the possibility of saving it online. Simply click "Convert".   Img2Go will begin the conversion process, so that you can download the most current JPEG  picture  as well as other images. JPG File Information JPG Images are compression image formats that allow data storage for digital information about images.   They may also be called"raster images" a"raster" image format.   Since compression is an essential component of JPG it could result in JPG's quality JPG becoming less than ideal in the process of download JPG files.   JPG is created from uncompressed camera images similar to NEF or CR2. Photos and  photos  stored as JPG format are frequently used on the internet because of...
Read more