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 most well-known language usedin numerous computationsin Power BI, many do not realize this function in Power Query. In this blog post I will show how easy for you to calculateAge using PowerBI using PowerBI. This methodis extremely beneficial in cases where you need for calculate the the calculations of age.can be made by calculating the row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table, which is made up of AdventureWorksDW table which includes the column for birthdates. I've removed columns that weren't needed for clarity;

In order to calculate the age of each client has reached, the only thing you'll have to do is:

  • In Power BI Desktop, Click on Transform Data
  • In the PowerQuery Editor window, select the first column, birthdate.
  • Click on the Add Column Tab, then under "From Date & Time" section, and finally under Date Select the age range.

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

But, the age appears on the Age column does not appear to be an age. It's because it's an actual duration.

Duration

Duration is a special type of data that is used by Power Query which represents the variations between two DateTime values. Duration is a mixture of 4 numbers

days.hours.minutes.seconds

This is how to get the above data. However, from the perspective of the user, it's not their responsibility to understand the specifics of this. There are techniques to make each section of time. By selecting the Duration menu you'll see the amount of seconds, minutes, hours days , and years.

To assist to assist calculating the age in years for instance it is easy to select Total Years.

It is important to note this: the measurement is in day and then divided by the number of days in order to calculate the value for the year.

Rounding

At the final point, nobody claims that your age is 53.813698630136983! They say 53, which is rounding down. It's simple to select Rounding and then round down option in the Transform tab.

This will give you the years-old age:

You can then clean the other columns if you wish (or this could mean because you made use of transformations within the Transform tab to avoid creating new columns) The column may be named Age: column Age.

Things to Know

  • Refresh The age calculated by this method is updated each time you refresh your data. Every time, it will align the birth date with the date and the time that the data refresh took place. In this method involves an algorithm used for pre-calculating the age. If, however, you need the calculation of the age to be performed dynamically by DAX In this article, I've explained the method that you can employ.
  • Why is this? Power Query The benefit of using age calculation with Power Query is that the calculation takes place at the time of refresh of your report. It is done using an application that facilitates the calculation, and there is no additional cost in doing it using DAX as a measurement of the runtime.
  • Other scenarios It isn't a method to calculate the age of a product solely based on birth date. This is a method to calculate the age of inventory of products and also the differences between dates or times 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 over twenty years' experience in area of data analysis, BI, databases, programmingand development mostly using Microsoft technologies. He has been an official Microsoft Data Platform MVP for nine years (from 2011 to the present) for his dedication for Microsoft BI. Reza has a reputation for being a prolific author and is co-founder with RADACAD. Reza is also the co-organizer and co-founder 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 written a couple of titles in the field of MS SQL BI and also is working on other books. He was also a regular member of technical forums on the internet such as MSDN as well as Experts-Exchange and was moderator on the MSDN SQL Server Forums, and holds the MCP and MCSE as well as an the MCITP for Business Intelligence. He is the leader of the New Zealand Business Intelligence users group. He is also creator of the highly sought-after Book Power BI from Rookie to Rock Star, which is free and offers over 17000 pages of content as well as Power BI Pro Architecture, which is 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 User Groups. And He is a Microsoft Certified Trainer.
Reza's mission is to assist you to find the most effective data solution. He is a Data enthusiast.This post was posted by Reza on Power BI, Power BI from Rookie to Rockstar, Power Query and was tagged as Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. Bookmark the permalink.

Post navigation

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

Comments

Post a Comment

Popular posts from this blog

reactiveEnergy-converter

Image
What's an angle ? The angles that can be acute or obtuse or narrow. An angle is the shape caused by two rays, which share the exact starting point , which is known as vertex. vertex. It is possible to ask What are the advantages of angles? it is possible to calculate the size of a tower if you know how far it is from where you are and also the angle between its top to the ground. With this method, there is a way can determine the size of the moon is, or, if you have the appropriate tools, the circumference of our globe. Should you toss something, and you'd like to know the distance it travels it is necessary to determine the angle at which the object is thrown at. There are many different fields that can benefit from angles however for now, we'll only focus on the fundamentals to geometry. Angles are classified based on their dimensions: WHAT IS A FREQUENCY CONVERTER ? A frequency converter, also called a power frequency converter is a piece of equipment that is ...
Read more

volumeFlowRate-converter

Image
What's the definition of an angle ? An acute, obtuse angle An angle is the shape created by two rays who share an origin point, referred to as vertex. vertex. One might wonder what are the advantages of angles? You can determine the height of a building if you know its distance from your location, as well as angles between the top of the tower and ground. Through the same technique , you can determine how large the moon is or, using the appropriate tools, determine the circumference of our planet. If you throw something, and you wish to know how far it'll be able to travel, it's important to be aware of the angle at which you throw it at. There are many other fields that require angles but in the present let's examine the fundamentals of geometry. Angles can be divided by their size: WHAT IS A FREQUENCY CONVERTER ? A frequency converter, sometimes known as a power frequency converter , is a device that accepts energy from an incoming source, typically at 50 o...
Read more