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 widely used language usedin numerous calculationsin Power BI, many don't know about the functionality available in Power Query. In this blog post I'll explain how easy it is to calculateAge in Power BI and Power BI. This methodis very beneficial when you need to calculate your estimation of your agecan be carried out on a pre-calculated row by row basis.

Calculate Age from a date

Below is the DimCustomer table that is part of the AdventureWorksDW table which serves as the "birthdate" column. I've removed columns that aren't needed to make it easier to read.

To calculate your age for each consumer, all you need is to:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; start by choosing the Birthdate column.
  • Go to the Add Column Tab, follow"Add Column." Then, click the "From Date & Time" section. Next, click on Date, select the appropriate age.

It's that simple. This does calculate any difference to the Birthdate column, as well as the current date and hour.

However, the age which can be seen in the Age column does not seem to look like an age. This is because it's a Duration.

Duration

Duration is a distinct kind of data type utilized by the data type used in Power Query which demonstrates the difference between the Two DateTime values. Duration is made up with four numbers.

days.hours.minutes.seconds

This is the best way to look up the values above. For the users' perspective they shouldn't be expected to find information such as this. There are methods by which you can get every single segment that's durational. If you go to the Duration menu, you'll discover that you can calculate the number of seconds minutes, hours days, and years.

To use it for calculating the age in years such as this, it is easy to get Total Year:

The duration is calculated in terms of days, then it is divided by the number of days in order to calculate an annual figure.

Rounding

In reality, no one claims one's age was 53.813698630136983! They say 53 and then round it down. You can choose Rounding and then select the round down from the Transform tab.

This will give you the age in years:

Make sure that the other columns are cleaned If you wish (or it could be because you used transformations with the Transform tab to avoid creating new columns) This column should be named column"Age"

Things to Know

  • Refresh The date calculated in this method will be refreshed each time you're refreshing your data. And each time, it will compare dates of birth and date and timing for the refreshing. This method is the initial calculation of the age. If you require your age calculation to be executed dynamically, using DAX This is what I've explained the best way to utilize.
  • How to use Power Query: Benefits of using age calculations in Power Query is that the calculation occurs at the moment of refreshing your report. Your report will be refreshed by using an application that makes calculations simpler, and it's not a cost-plus benefit of calculating it using DAX as measure of time.
  • Another situation is that it isn't meant to calculate the age solely on birthdates. This is a way to calculate the inventories of products as well as the different between two dates, or times, from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He is a BSc from Computer engineering. He holds greater than twenty years of experience in the field of data analysis and BI, database programming and development that is primarily with Microsoft technologies. He has been a Microsoft Data Platform MVP for nine consecutive years (from 2011 to present) due to his commitment towards Microsoft BI. Reza is a prolific blog author, and also the editor and co-founder of RADACAD. Reza is co-founder, as well as coordinator 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 frequent forum participant on online forums for technical issues , such as MSDN and Experts-Exchange, and was moderator of MSDN SQL Server forums, as well as an MCP and MCSE as well as an MSCITP of the BI. He is the director of the New Zealand Business Intelligence users group. He is also the editor of the book that is extremely well-loved Power BI from Rookie to Rock Star that is completely free and includes an additional 1700+ pages of content and a companion book called Power BI Pro Architecture published by Apress.
His credentials are that he is an International speaker at 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 helping users find the best data solution. He is a Data enthusiast.This post was filed under Power BI, Power BI from Rookie to Rockstar, Power Query and is classed with Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. You can follow any responses to this entry through the RSS feed.

Post navigation

Share Different Visual Pages using different Security Groups Power BIAge's years Calculation is able to calculate Leap Year in Power BI using Power Query

Comments

Post a Comment

Popular posts from this blog

  What's the  most complete version of RIP (i) RIP: Rest In Peace rip stands for  the word "rest" means Rest In Peace.   It's a phrase  commonly  written upon the tombs of Catholics to wish them eternal rest in peace after they have passed away.   It is called Rest In Peace because we recognize that when people die, it's like their resting.   A similar phrase is mentioned in the Bible of Isaiah. "... will arrive with peace, and they will sleep peacefully in their beds. The one who goes straightforward. " The phrase appears on headstones as RIP or R.I.P.   It is also used to ask an individual soul to be able to find peace and eternal happiness after  the death . This phrase became very common to use on the tombs of Christians in 18th century.12.7M242Difference between JDK, JRE, and JVM (ii)  rip meaning  Routing Information Protocol It is the Routing Information Protocol (RIP) is one of the earliest route-based on a distance pro...
Read more
  rip full form  & History: As you may know, RIP can be described as an  Abbreviation and Short forms things.   According to use RIP is most frequently used in Christians customs since they don't burn dead bodies , they simply place them in graves and write RIP in the graves for the Soul's Peace. This is the name given to the burial grounds of  Catholics to wish them forever peace.   When people die, it is known as Rest in Peace because we know that it's akin to their repose when people die. Statement according with the Bible of Isaiah.   There is a belief that God will judge the Living objects on judgment day; up to the point when the body will lay in rest. The words are usually displayed on headstones in the form of RIP or R.I.P.   It is also, used to pray for a soul to find peace and eternal happiness in the afterlife. The 18th century was when it was  common in funerals and tombs belonging to Catholics and other Catholics, who believe...
Read more

influencer meaning in tamil

  influencer meaning in tamil Micro-Influencers Micro-Influencers Micromicro  influencers which means in Tamil  are people with a deep understanding of specific niche.They are popular for their knowledge and enjoy a huge following in social networks. Influence isn't just determined by followers, as well as how the micro-influencer interacts with his or his followers. Micro-influencers can be said to comprise between 1,000 and 40,000. Micro-influencers might not be aware of the existence of a business until they attempt to contact. If that is the case then the business must convince the influencer of its worth. Micro-influencers are experts who won't disappoint their followers if they advocate lemonade. Influencers are selective regarding the brands they work with due to the need for micro-influencers and brands to align with their intended audiences. Certain microinfluencers or micro  influencers will endorse the brand for free.   Some may require the payment. ...
Read more