Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. However, because DAX is the most commonly used language usedin numerous calculationsin Power BI, many do not have a clue about the option available within Power Query. In this blog post I'll discuss how easy to calculateAge in Power BI with Power BI. The methodis exceptionally useful in situations where your age calculationcan be made on a pre-calculated row by row basis.

Calculate Age from a date

This is the DimCustomer table from the AdventureWorksDW table that functions as the birthdate column. I've removed columns that aren't required for ease of understand.

To calculate your average age of each consumer all you have to do is:

  • In Power BI Desktop, Click on Transform Data
  • On the Power Query Editor window; start by selecting the column titled Birthdate.
  • go to Add Column Tab, and select"Add Column" and then on "From Date & Time" section. Then, under Date, select the appropriate age.

That's it. This doesn't calculate any differences from the Birthdate column as well as the current date and the time.

However, the age which can be seen in the Age column, doesn't appear to be an actual age. This is because it's a Duration.

Duration

Duration is a specific kind of data type utilized by the data type used in Power Query which is used to represent the differences between the DateTime and DateTime values. Duration is a combination from four figures:

days.hours.minutes.seconds

This is how you find the above values. However, for users' standpoint, they shouldn't need to research information like that. There are many ways that are able to determine each segment that is the length of time. If you go to the Duration menu, you'll discover how you can determine the amount of seconds, minutes, hours, and years from it.

For calculating the age in years such as this, it is easy to hit Total Year:

The duration is calculated by days. Then, after it is divided by the number of days to provide an annual figure.

Rounding

It's the truth, no one claims one's age was 53.813698630136983! The people call it 53 and then they round it down. It is possible to select the Rounding option and then select the round down in the Transform tab for it.

This will give you the age in years:

Clean the other columns If you want to (or this could mean that you utilized transformations using the Transform tab, to avoid the need to create new columns), and call this column"Age"

Things to Know

  • Refresh The date calculated this way will get refreshed each time you're refreshing your database. Each time, it will check the dates of birth with the timing and date of the refreshing. This method is the initial calculation of the age. If you need the calculation of age to be continuously performed using DAX This is the method I've explained the best way to use.
  • The reason for Power Query: Benefits of making age calculations with Power Query is that the calculation is made at the time of refreshing your report. Your report will be refreshed using an application that makes calculations simpler, and it's not a cost-plus benefit of calculating it using DAX as measure of time.
  • A different scenario is that it's not intended to calculate age only by birthdate. This is used to calculate the inventory of goods and also the difference 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 holds a BSc of Computer engineering. He has an impressive 20+ years' knowledge of data analysis, BI, databases programming and development, focusing using Microsoft technologies. He was an official Microsoft Data Platform MVP for nine consecutive years (from 2011, to the present) due to his commitment to Microsoft BI. Reza is a regular blog writer, as well as the co-founder and editor of RADACAD. Reza is also the co-founder and coordinator of Difinity event that takes place within 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 writing other books. He was also a regular forum participant on online forums for technical issues such as MSDN and Experts-Exchange as well as a moderator of MSDN SQL Server forums, and holds an MCP and Microsoft Certified Specialist (MCSE) as well the MSCITP in BI. He is also the head of the New Zealand Business Intelligence users group. He is also the author of the book , which is highly praised Power BI from Rookie to Rock Star it is available for free and contains an additional 1700+ pages of content and a companion book called Power BI Pro Architecture published by Apress.
His qualifications includes being an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday along with SQL users groups. And He is a Microsoft Certified Trainer.
Reza's passion is to help users find the right data solution. He is a data enthusiast.This entry was published as Power BI, Power BI from Rookie to Rockstar, Power Query and is classified under Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

- Share Different Visual Pages using different Security Groups Power BIAge's Year Calculation, which works for Leap Year in Power BI with Power Query

Comments

Post a Comment

Popular posts from this blog

rip full form  & History: As you may know, RIP can be described as an  Abbreviation and Short forms things.   According to the usage RIP is most commonly used in Christians tradition since they don't burn dead bodies , but instead they place them in graves and write RIP in the graves to bring the soul to find Peace. It is written upon the tombstones of  Catholics to send them forever peace.   When people die, it is known as"Rest in Peace because we know that it's akin to their repose after death. Statement according with the Bible of Isaiah.   It is believed that God will be the judge of living objects on the day of judgment; until then, the body is to be at peace. The phrase is usually found on gravestones with the words RIP or R.I.P.   It can also be used to pray for the soul to find peace and eternal happiness following death. In the 18th century, it was  common in burials for Catholics who believed it was a prayerful wish to their souls th...
Read more

angle-converter

 <h2>  <a href="https://aboneapp.com/#/angle-converter">What is the definition for the word "angle"</a>? It is an angle that's acute, narrow or narrow. </h2> <p>  An angle is an <strong>shape formed by two rays that have an identical starting point known by the <em>vertex</em></strong>. It's all about what are the advantages of angles? It is possible to figure out the size of a tower when you know the distance it is from your house and angles between the surface and the top. By employing the same <a href="http://%2A%20https//en.wikipedia.org/wiki/Technique">technique</a> employing the same technique you can figure out the size of the moon's diameter or when you've got the right equipment and tools, determine the diameter of our planet. Additionally, if you throw something and want to know the distance it travels you'll have to know the angle of the throw. There are a vari...
Read more
What's the  complete form of RIP (i) RIP: Rest In Peace rip stands for  rest In Peace.   It's a term  commonly written upon the tombs of Catholics to wish them rest in peace after they have passed away.   It's called Rest In Peace because we are aware that when someone dies, it is like they are resting.   Similar words can be mentioned in the Bible of Isaiah. "... will be at peace and can rest on their beds. He who is simple. " The expression appears on gravestones with the letters RIP or R.I.P.   It can also be used to pray for the soul 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 among the oldest routes using a distance vector protocol.   It utilizes hop counts as a metric for routing.   In hop cou...
Read more