Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, since DAX is the main language usedin numerous calculationsin Power BI, many don't know about the function that is available in Power Query. In this article I'll explain how simple it is to calculateAge in Power BI using PowerBI. This methodis extremely useful for situations where the estimate of the agecan be done as an earlier calculated row-by-row row basis.

Calculate Age from a date

Below, you will see the DimCustomer table, which is part of the AdventureWorksDW table, which acts as an entry point for the birthday column. I've removed a few the columns in order in order to simplify the study.

In order to calculate your age for each client All you have to do is:

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

That's it. this is how you calculate an amount which is the sum of the Birthdate column, in addition to the current date and time.

However, the age displayed under"age" in the Age column, however, it does not appear to be an actual age. It's because it's actually a duration.

Duration

Duration is a specific data type within Power Query which represents the difference of two DateTime values. Duration is the combination of four values:

days.hours.minutes.seconds

This is the way to consider the above data. In the eyes of the user, it's not expected of them to look up the specifics of this. There are techniques that can extract every bit of information from the time. If you choose"Durnancing" in the Menu, you'll see that you are able to calculate the amount of seconds and minutes, hours, days and years out of it.

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

Take note that the duration of the time is measured in days . After that, it is then divided by the number of days in order to get the annual sum.

Rounding

In reality, no person declares that they are 53.813698630136983! They are 53, which is rounded down. It is easy to select Rounding and round down on the Transform tab.

This will show you your age in years:

Then, you can clean the other columns, if you wish (or maybe you've made use of transformations with the Transform tab to not have you create new columns) And name this column"Age:

Things to Know

  • Refresh The age calculated this way will be changed each time you're refreshing your information. Every time, it will match your birthdate with the date and time at the time of refresh. This method is the pre-calculation of age. If, however, you require that the calculation be performed dynamically by DAX this is the way I explained the method that you can use.
  • The rationale for Power Query: Benefits of doing an age calculation using Power Query is that the calculation takes place while you refresh the report. The report is refreshed using an instrument that makes the calculation easier and faster, and there's no extra cost for calculating it with DAX to measure the time.
  • Alternative scenarios This isn't utilized to calculate the age from birthdate. this can be used for the age of inventory at the stock level and for the variations between two dates and times 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 on Computer engineering. He has more than 20 years old. experience in the area of data analysis, databases, BI and development with a focus on Microsoft technologies. He is a Microsoft Data Platform MVP for nine years running (from 2011 to the present) because of his enthusiasm with Microsoft BI. Reza is a prolific blogger who is also the co-founder as well as director of RADACAD. Reza is also the co-founder and coorganizer of the Difinity Conference 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 additional books. He was also a frequent participant in forums online for technical issues , such as MSDN and Experts-Exchange and was also moderator of MSDN SQL Server forums, and is an MCP and MCSE and an MCITP of Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. In addition, he is the author of the well-known publication Power BI from Rookie to Rock Star, which is free and has more than 170 pages of content. the Power BI Pro Architecture published by Apress.
Speakers are 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 passion is to help users to find the ideal data solution. He is an avid Data enthusiast.This report was written by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is categorized within Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

- Share different visual pages using different security groups in Power BIAge Calculation in Years which works for Leap Year in Power BI through Power Query

Comments

Post a Comment

Popular posts from this blog

Scientific Calculator

You're allowed to use a calculator for the duration of the Math Test. But you need to do more that just making sure you have a new supply of batteries.Important: Bring Your Own Calculator Calculators aren't offered at the test centers, and you cannot use a calculator . Quick Facts Only the calculators listed here are accepted for the Math Test - Calculator portion of the test. You may not make use of any calculator while working on tests like the Reading, Writing and Language or Math Test--No Calculator portions, and must take away the calculator aside during these sections of the test. You'll be kicked out and your marks canceled in the event that you use your calculator for sharing information during the exam, or to remove questions or answer sheets from the testing room. Calculator Smarts Bring your own calculator. You can't share one. Don't bring a brand new calculator. Bring one you're comfortable with. Prepare...
Read more

angle-converter

Image
What is An angle ? Angles that are acute, obtuse, or narrow. An angle is a shape formed by two rays. Both have a common point of origin, called the vertex . It's possible to wonder why angles are crucial? It is possible to estimate the dimensions of a tower if you are aware of the space between the tower and you, as well as the angles between the surfaces that make up the roof of the tower. With the precise technique the possibility exists to determine the size of the moon is or, with the appropriate tools, the radius of our planet is. Additionally, if you throw something and are trying to figure out the distance it will travel, you'll have to consider what angle the object will be throwing it from. There are many other fields that require angles but, for the moment, let's focus on the basics of geometry. Angles are classified according to their dimensions: WHAT IS A FREQUENCY CONVERTER ? The frequency converter could be also referred to as power frequency conve...
Read more

energy-converter

Image
What is the definition of angles ? A angle which is acute, narrow or narrow. An angle is a form formed by two Rays. They have a common starting point that is called the vertex. The question to be asked is which are the advantages of angles? It is feasible to figure out the top of a building when you know the distance it is from your location, and the angles between the surfaces and the top. By using the same method you are able that you find out the size of moon's circumference is, or if you have the proper tools to measure it and the dimension of our planet. Also, if you throw something and wish to determine the distance it travels, you'll need to be aware of the angle from which the item is throw. There are many applications where angles are beneficial however, for the moment being let's look at the fundamentals of geometry. Angles can be classified into their dimensions: WHAT IS A FREQUENCY CONVERTER ? Frequency converters, often described as power-frequency ...
Read more