Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way for calculating the age. But, as DAX is the preferred languagein several analysisin Power BI, many don't have any idea about the functions available in Power Query. In this blog I'm going to discuss how easy to calculateAge in Power BI, using Power BI. This methodis extraordinarily effective for situations where the age calculationcan be made by calculating the row-by-row basis.

Calculate Age from a date

It's the DimCustomer table from the AdventureWorksDW table that includes an age column. I've removed some of the extra columns so that it is easier to read;

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

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

That's all there is to it. This is able to calculate an amount that is the total of the column for Birthdate column, in addition to the current date and time.

However, the age that you see under the Age column doesn't seem to be an actual age. This is because it's a duration.

Duration

Duration is a distinct data type that is found in Power Query which represents the distinctions between two DateTime values. Duration is made up of four different values:

days.hours.minutes.seconds

and that's how you can consider the data above. However, from the viewpoint of users you don't want them to find specifics such as that. There are ways you can fetch each part from the length. When you select the Duration menu, it will show you can find the amount of seconds and minutes, as well as hours, days, and years from it.

To apply this method of calculating the age in years such as, for instance, you can simply hit the Total Year:

It is important to note that the length of this program measured in terms of days. It is then divided by 365, which will calculate the value for the year.

Rounding

At the final point, there is no consensus that says that the Age is 53.813698630136983! They say 53, and rounding it down. You can select Rounding and Round Down in the Transform tab.

This will give you an estimate of your age in years:

Then , you can clean the other columns, if you wish (or perhaps you've applied transformations by using the Transform tab to avoid the new columns) And name this column as Age:

Things to Know

  • Refresh The age determined through the method shall be updated when the time comes that your data is refreshed. And each time it will compare your date of birth to the date and date on which the refresh of data took place. This method allows for an earlier age calculation. If you want your calculation to be done dynamically with DAX Here's a detailed explanation of how to use it.
  • How to utilize Power Query The advantage that come from performing age calculations using Power Query can be that this calculation is made in the course of updating your report. This is done making use of an instrument which makes the calculation simple, and it won't be additional cost in doing it with DAX for determining runtime.
  • Additional scenarios for calculating age only by the date of birth. This is an excellent method to measure inventory for products as well as for the differences between two dates or dates each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc at the level of Computer engineering. There are more than 20 years of experience in the field of data analysis, database, BI, programming and development that is primarily with Microsoft technologies. He has been named an official Microsoft Data Platform MVP for nine years (from 2011 till now) for his commitment to Microsoft BI. Reza is known as a prolific writer and co-founder at RADACAD. Reza is also co-founder and co-organizer of the Difinity conference 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 couple of book in the field of MS SQL BI and also is writing a few additional. He was also a regular forum participant on online forums for technical issues , such as MSDN and Experts-Exchange . He was the moderator of MSDN SQL Server forums and holds the MCP and the MCSE. He is also an MCITP of BI. He is the director of the New Zealand Business Intelligence users group. Also, he is the writer of the book that is extremely well-loved Power BI from Rookie to Rock Star, which is completely free and comprises hundreds of pages of material in addition to The Power BI Pro Architecture published by Apress.
It is an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday in addition to SQL group for users. And He is a Microsoft Certified Trainer.
Reza's goal is to help users find the best data solution. He is a Data enthusiast.This post was released within Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. This article is a good resource to save.

Post navigation

- Share Different Visual Pages with Different Security Groups Power BIAge's Age Calculation which is used to calculate the Leap Year in Power BI by using Power Query (see below).

Comments

Post a Comment

Popular posts from this blog

full form of the ncc

  about ncc The National Cadet Corps (  ncc full form )is the Indian military cadets' organization and has its headquarters at New Delhi.   It is open to college and school students on a non-committal basis.The National Cadet Corps in India is a non-profit organisation which recruits cadets from  high schools , colleges and universities across India.   Cadets are provided with basic military instruction on the use of small arms as well as parades.   Cadets and officers are not under any required to join military service once they have completed their education, however they are given a greater preference than other candidates in selections based on their performance within the Corps. meaning of ncc "Unity and Discipline (Ekta aur Anushasan)" Four Cardinal Principles of Discipline Obey and smile Be Punctual Stay focused and unflinching. Don't make excuses, and do not lie about anything. Aims of NCC 1.To develop the character and courage of a person, and also...
Read more

ncc full form

  about ncc The National Cadet Corps (  ncc full form )is the Indian military cadets' corps that has its headquarters located situated in New Delhi.   It is accessible to both college and high school students on a voluntary basis.The National Cadet Corps in India is a non-profit institution that recruits cadets from  high schools , universities and colleges across India.   Cadets are provided with basic military instruction on the use of small arms as well as parades.   Officers and cadets are no obliged to enter military service after they have completed their education, however they receive a higher importance than normal candidates when it comes to selections depending on their achievements within the Corps. meaning of ncc "Unity and Discipline (Ekta aur Anushasan)" The four Cardinal Principles of Discipline Obey and smile Be Punctual Focus and remain unflinching. Do not make excuses and don't lie. Aims of NCC 1.To develop character traits and courage, a...
Read more

Online Age Calculator: Find Out How Old You Really Are!

Image
 If you want to know how old you are, there are a few ways to calculate it. You can use an online age calculator, which is a convenient option if you don't know your exact birth date or if you want to know how old you are in a different culture. You can also use your birthday and the current date to calculate your age, or you can ask someone else to do it for you. The age of a person can be counted differently in different cultures. This calculator is based on the most common age system. In this system, age is expressed by counting years with or without including the current year. For example, one person who is twenty years old is the same as one person who is in the twenty-first year of his/her life. In one of the traditional Chinese age systems, people are born at age 1 and the age grows up at the Traditional Chinese New Year instead of birthday. For example, if one baby was born just one day before the Traditional Chinese New Year, 2 days later, the baby will be at age 2 even th...
Read more