Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. But, because DAX is the largest and most commonly used language usedin numerous calculationsin Power BI, many are unaware of this feature in Power Query. In this blog post I will demonstrate how simple for you to calculateAge using Power BI in Power BI. The methodis extremely helpful for situations in which you require be able to calculate the calculation of age.can be made on a previously calculated row-by -row basis.

Calculate Age from a date

Here is the DimCustomer table, which is one of AdventureWorksDW table which contains the column for birthdates. I've removed some of the columns that don't need to be there for clarity;

To calculate the age of each client is, all you need to do is:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; choose the first column, Birthdate.
  • To add a column, click on the Column Tab and then click on the "From Date & Time" section, and under Date Choose the appropriate age range.

This is all there is to it. This is the method to calculate what's the proportion between the Birthdate column as well in the current date and time.

However, the age that appears in the Age column does not appear to be an age. This is due to the fact that it's an actual duration.

Duration

Duration is a special type of data used for Power Query which represents the difference between Two DateTime values. Duration is made up from four different numbers.

days.hours.minutes.seconds

This is how you read the above values. But from the perspective of the user, it's not their responsibility to understand the specifics of this. There are ways to make each section of time. By selecting the Duration menu you'll be able see the quantity of minutes, seconds, hours, months and days from it.

In order to aid the process of calculating the age in years such as, for instance it is simple to choose Total Years.

It is vital to keep in mind how the length is calculated in days, and following that, divided by the number of days to provide you with the annual amount.

Rounding

At the final point, nobody declares that your age is 53.813698630136983! They claim 53, which is reduced to. It's very easy to select Rounding then then the Round Down option from the Transform tab.

This will tell you the years-old age:

After that, you are able to clean the other columns if you wish (or it could be that you have used transformations in the Transform tab to stop the creation of new columns) The column may be renamed as Age the column.

Things to Know

  • Refresh The age calculated by this method will be updated whenever you refresh your data. Each time, it'll match the birth date with the date and date that the refresh was completed. It is a method involves an algorithm used for pre-calculating the age. If, however, you want the calculation of the age to be performed dynamically by DAX This article will explain the method that you can employ.
  • What's the reason? Power Query: Benefits of using age calculation with Microsoft Power Query is that the calculation is carried out while you refresh your report. It's performed with an application that makes the calculation easier and there is no additional cost in doing it using DAX as a measure of time.
  • Additional scenarios This is not a method to calculate age solely by birthdate. It can be used to calculate the time of inventory for items and also to determine the differences between dates and times that differ from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds an BSc of Computer Engineering. He has more than twenty years of experience in the field of data analysis and programming, BI, databases and development that is mainly based on Microsoft technologies. He has been an official Microsoft Data Platform MVP for nine consecutive years (from 2011 to the present) for his dedication towards Microsoft BI. Reza is known as a prolific writer and co-founder with RADACAD. Reza is also co-founder and co-organizer 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 has also written a few works regarding MS SQL BI and also is writing other books. He also was a frequent participant on technical forums online such as MSDN and Experts-Exchange and was moderator on the MSDN SQL Server forum, and holds the MCP and MCSE as in addition to the MCITP for BI. He is the founder of the New Zealand Business Intelligence users group. He is also responsible for the creation of the highly loved publication Power BI from Rookie to Rock Star, which is free and contains greater than 17,000 pages of data and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
This speaker has been an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday as well as SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help you identify the most effective data solution. He is a Data enthusiast.This article was published by Reza on Power BI, Power BI from Rookie to Rockstar, Power Query and was tagged with Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. The following entry was filed under Power BI.

Post navigation

Share different visual pages using different security groups in the Power BIAge's Year Calculation that is used to calculate the Leap Year in Power BI through Power Query

Comments

Post a Comment

Popular posts from this blog

counting in hindi

Image
  counting in hindi Numbers are strings of numbers that are used to represent the size.   They indicate size, i.e. how big or small the quantity is.   In math, there are many kinds of numbers. However, they belong to two primary classes: counting numbers, and scalars. Counting numbers , Natural Numbers These are used for counting the amount of objects.   It is a positive number with zero fractional parts.   For example 12 cars,  45 students , three houses.   For more information, refer to Counting numbers and Natural numbers. Scalars They are  numbers in Hindi  employed to measure an amount to any desired level of precision.   For example a building height is 12.388 meters or the speed of an aircraft has been measured as 810.31 kilometers per hour.   They can be decimal or fractional components.   See also Scalar definition.   Within this category , there are various kinds of numbers: Real numbers are ones which can be pos...
Read more

reactiveEnergy-converter

Image
What is an angle ? An angle that is acute, narrow, or narrow. The shape is formed by two rays that have exactly the same starting point that is known as vertex. vertex. It is possible to ask: What are the benefits of angles? It is possible to determine how big a structure by knowing how far it is from you and also the angle between to the summit of the building to the ground. Utilizing the same method, there is a method to determine how big the moon is or, if you have the right tools, estimate how much circumference we have of our own earth. If you throw something and want to know the distance it travels , you must know the angle at which you throw it. There are many other situations that could benefit from angles however at this point we'll concentrate on the fundamentals of geometry. Angles can be classified by their dimensions: WHAT IS A FREQUENCY CONVERTER ? Frequency converter, also known by the name of a power frequency convertor . It is a device that takes the pow...
Read more

volumeFlowRate-converter

Image
What is an angle ? Obtuse, acute angles An angle is a form formed by two rays sharing the same starting point, also known as vertex. vertex. You might be wondering what are the advantages of angles? You can estimate the height of a tower by knowing its distance from you, and also the angles between top of the structure and the ground. With the same method , you can determine how big the moon is or, with the appropriate tools, determine the circumference of our own planet. If you throw something and you want to know how far it'll travel, you'll have to be aware of the angle at which you throw it. There are a myriad of other fields that require angles however, for the time being let's take a look at the basic principles of geometry. Angles can be classified by their dimensions: WHAT IS A FREQUENCY CONVERTER ? A frequency converter, also known as a power frequency converter , is a gadget that accepts power from the incoming source, usually at a frequency of 50 or 60...
Read more