Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way that is a simple method of calculating the age. However, because DAX is the most popular language usedin several calculationsin Power BI, many do not know about the function available in Power Query. In this blog article I'll describe how simple it is to calculateAge in Power BI using PowerBI. It is a methodis very useful when it is necessary to do the age calculationcan be calculated on an earlier calculated row by row basis.

Calculate Age from a date

This is the DimCustomer table , which is from the AdventureWorksDW table that acts as the birthdate column. I've removed the columns that aren't needed to make it more readable;

For you to calculate your age for every buyer, 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 the add Column Tab, which is under the "From Date & Time" section, and under Date, select Age

That's all there is to it. This calculates the calculate any difference in the Birthdate column, and the current date and time.

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

Duration

Duration is a special type of data that is used in Power Query which represents the difference in two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

and that's what you will see in the information above. However, from users' perspective they shouldn't be expected to go and read the details like the ones above. There are ways you can retrieve each component of the time. Utilizing the Duration menu, you will see that you can extract the amount of seconds, minutes, hours days and years out of it.

To assist in calculating the age in years for instance it is easy to hit Total Year:

Note that the duration is measured in days and was then divided in 365 to yield the yearly amount.

Rounding

It's the truth, no one states the age of their child as 53.813698630136983! They say 53, which is rounded down. It is easy to select Rounding option and then round down on the Transform tab for it.

This will provide you with the number in years:

It is then possible to clean other columns if you'd like (or there is a chance that you have applied transformations from the Transform tab to avoid creating new columns), and call this column; Age:

Things to Know

  • Refresh The age that is calculated this way will get changed at the time of refreshing your database. Each time, it will match the birthdate to the date and date that the data refresh took place. This method is an earlier calculation of age. If, however, you want the age calculation to be carried out dynamically, using DAX This is the way I've explained how to make use of.
  • The reason behind Power Query: Benefits of performing an age calculation using Power Query is that the calculation is performed when you refresh your report. It is done using an algorithm that makes the calculation more simple, and there won't be extra overhead in calculating it using DAX as a measure runtime.
  • Different scenarios This is not intended for the calculation of age by birthdate. This could be used for product inventory age and the differing dates and dates from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds earned a BSc from Computer engineering. He have more that 20 years of experience in data analysis and BI, databases, programming, and development mostly using Microsoft technologies. He has been a Microsoft Data Platform MVP for nine consecutive years (from 2011, until now) for his dedication in Microsoft BI. Reza is a frequent blog writer, and the co-founder and editor of RADACAD. Reza is also co-founder as well as co-organizer of Difinity the conference held 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 wrote several books on MS SQL BI and also is working on other books. He was also a regular participant in online forums for technical issues such as MSDN and Experts-Exchange, and was moderator of MSDN SQL Server forums, and is an MCP and MCSE as well as an MCITP in BI. He is the founder of the New Zealand Business Intelligence users group. In addition, he's also the author of very popular book Power BI from Rookie to Rock Star, which is available for free and has more that 1700 pages of content and 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 users groups. And He is a Microsoft Certified Trainer.
Reza's dream is to help you find the ideal 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 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 Calculation of Years 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

Random number generation Random number generation is a process in which, generally via an random number generator (RNG) or a random sequence of symbols or numbers that can't be reliably predictable better than random chance is created. This implies that the resultant sequence could contain patterns that are discernible in hindsight but unpredictable to the foresight. The real random number generators can be hardware random-number generators (HRNGS) that produce random numbers, and each generation is determined by the current value of a physical environment's attribute which changes continuously in a way that is nearly impossible to comprehend. This would be in contrast to so-called " random number generations" done by pseudorandom number generators (PRNGs) that generate numbers that only look random but are in fact pre-determined--these generations can be reproduced simply by knowing the state of the PRNG. Various applications of randomness have led t
Read more

What does DSP mean? dsp full form

  What does DSP mean ?   dsp full form The blog in this article, you will be reading about dsp full form, dsp full form dsp full form in hindi..   The entire information is available inside this article.   The blog offers complete information on the dsp full form, dsp full complete the form dsp full form in hindi..   I hope that you enjoy this blog post. DSP is an Indian police officer of the grade.   DSP is the state representative. the State, who supervises the state officers.   The rank symbol of the officers is that of the national symbol for the nation. It is placed on the shoulder strap over one star. DSP is equivalent to DSP is equivalent to (Assistant Commissioner of Police) and may be elevated to an IPS after a short time of time, as per legislation of the State.   Every year police officers must pass exams for appointment to the DSP level.   After the mentioned years of service, inspectors are frequently elevated to DSP. Criteria for eligibility to (  dsp full form ) DSP Indi
Read more

LENGTH CONVERTER

  LENGTH CONVERTER Select the length you'd like to utilize.   Then , enter the amount that you would like to convert into a new length measurement in the "From" field, and choose the measurement you wish to convert it to within the "To" field.   The result of the conversion will be displayed on the "To" field right after you click on any number. "Length" or the term"length," which you've likely encountered It is defined by the word "length" as a precise  measurement  of anything in between another.   It's hard to find exact measurements that convert miles to kilometers.   Sometimes, we require an estimation of the time it will take others to get to the destination or how long it takes to get there which is why length converters can be useful for calculations.   Measurement  Converter can be used in accordance to the requirements and because it can transform any length into a different one, it could assist you with
Read more