Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, as DAX is the primary language usedin numerous computationsin Power BI, many don't know about this function in Power Query. In this article I'll explain how easy it is to calculateAge with Power BI with Power BI. The methodis extremely beneficial for situations where the estimation of your agecan be calculated on an earlier calculated row-by-row row basis.

Calculate Age from a date

Below you can see the DimCustomer table which is comprised of AdventureWorksDW table, which is the birthdate column. I've removed some of additional columns making it simpler to understand.

In order to calculate your age for each of your customers all you have to do is to:

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

That's that. this is how you calculate the amount which is the total of the column for Birthdate column, as well as the current date and time.

The age, however, which appears in"Age" but, since it's under the Age column, but it doesn't appear to be a true age. It's because it's actually a time period.

Duration

Duration is a special form of data that is part of Power Query which represents the variation between the two DateTime values. Duration is the combination of four numbers:

days.hours.minutes.seconds

This is the way to consider the above data. However, for the viewpoint of the user, it's not the responsibility of them to study the entire details of this. There are techniques that can capture every single part of the period. When you select the Duration menu, you will see that you are able to determine the number of seconds and minutes or hours, days, and years from it.

To assist with calculating the age in years like, for instance you can hit the Total Year:

The duration is calculated in days . It is after that was divided by the days, to yield the yearly amount.

Rounding

No one says they're 53.813698630136983! They use the word 53 which is rounded down. It's simple to choose Rounding and round down on the Transform tab.

This will show you your age in years:

You can then clean the other columns, if you wish (or maybe you've taken advantage of transformations through the Transform tab to not have the task of creating new columns) This column can be renamed as follows: Age: column Age:

Things to Know

  • Refresh The age calculated in this manner is changed each time you're refreshing your information. and each time will compare the birth date to the date and timing at the time of refresh. This method is a pre-calculation of an age. If, however, you require the calculation be made dynamically using DAX here's the method I used to describe the method you can apply.
  • The reasoning behind Power Query: Benefits of performing an age calculation with Power Query is that the calculation is made during the refresh of your report. It is an instrument that makes the calculation more efficient and speedier, and there's no extra cost in calculating it using the DAX method to determine runtime.
  • Alternative scenarios It is not utilized to calculate the age of a person based on their birth date. This is a good way to determine an inventory level age calculation and the differences between two dates and times from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds the BSc on Computer engineering; he is more than 20 years old. experience in the field of data analysis and databases, BI and development primarily in Microsoft technologies. He is an official Microsoft Data Platform MVP for nine years in a row (from 2011 until today) due to his passion of Microsoft BI. Reza is a prolific blogger as well as the co-founder and director of RADACAD. Reza is also co-founder as well as co-organizer for 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 writing more books. He was also a frequent participant in online forums dealing with technical issues , such as MSDN and Experts-Exchange and was the moderator of the MSDN SQL Server forums, and holds an MCP and MCSE as well as an MCITP of Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. The group also is creator of the highly acclaimed book Power BI from Rookie to Rock Star, which is available for free and includes more than 1700 pages of content and Power BI Pro Architecture, which is 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 and SQL Users Groups. And He is a Microsoft Certified Trainer.
Reza's passion is to help users find the best data solution. He is an avid Data enthusiast.This post was written by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is categorized into Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

- Share different visual pages with different Security Groups within Power BIAge's Years Calculation which is applicable to Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

Scientific Calculator

What Is A Scientific Calculator Digital calculators were around for centuries, and they've gotten progressively better. But the first calculator that was scientifically designed and made available to the general public just a few decades ago, in 1968. These devices are now a common sight in classrooms around the world. But what do they actually mean? What is more, how do they different from basic calculators? I've outlined the tool's history features, functions, uses and the differences between regular and scientific calculators in this piece to help you begin using the program. What is a Scientific Calculator ? Scientific calculators can be described as digital calculators packed with functions to help solve engineering, science, and mathematics-related problems. They allow users to solve trigonometry-related problems by using the trigonometric, inverse as well as hyperbolic, functions. Users can also work with degrees, radians and grads by simply ...
Read more

scientific calculator

Online Scientific Calculator This scientific calculator, which we provide online free of charge and is capable of performing the known as "normal" operations, also has the unique benefit of having a wide array of other functions, like solving second and first degree equations, converting various units of measurement, execute operations using percentages, work with complex numbers, and automatically calculate integrals and more. A lot more than just a basic scientific calculator, this small online calculator permits calculations to be introduced using traditional mathematical notation. In this way, the perception of the operation to be calculated is facilitated and the possibility of introducing unwanted errors is reduced.https://web2.0calc.com/widgets/horizontal/ Try, for example, using some of these calculations : Simple arithmetic expression First degree equations: Second degree equations: Calculation of measures using: Measuremen...
Read more

energy-converter

Image
What's the meaning behind An angle ? The angle can be acute or narrow or obtuse. An angle is a design that is created by two radiations. They share a common beginning point that is known to be the vertex. There's a question to ask: what are the benefits of angles? It is possible to estimate the height of a tower , provided you know where away it is from the location you live in as well as angles between the surface as well as the tower's top. By employing similar technique there is a possibility to calculate the size the moon. Or If you're equipped with the proper equipment it is possible to determine the circumference of the planet. In addition, if you throw something and wish to know the distance it travels, you'll have to know the angle at which you throw it. There are many areas where angles are beneficial but, at this point we'll concentrate on the basics of geometry. Angles are classified according to their dimensions: WHAT IS A FREQUENCY CONVERTE...
Read more