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 popular language usedin numerous computationsin Power BI, many do not have any idea about this feature within Power Query. In this blog post , I will describe how simple you can calculateAge with PowerBI using PowerBI. The methodis extraordinarily helpful for situations in which you require for calculate the calculations of agecan be made on a row-by row basis.

Calculate Age from a date

Here's the DimCustomer table that's part of the AdventureWorksDW table that is the column for birthdates. I've removed some of the columns that aren't necessary to make it easier to read;

To calculate the age of each customer has reached, the only thing you'll have to do is:

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

That's about it. This is how you calculate what is the ratio between the Birthdate column as well with the current date and time.

The age displayed in the Age column, but it doesn't appear to be an age. This is due to its actual length.

Duration

Duration is one of the types of data that is used in Power Query which represents the differentiating factors between the two DateTime values. Duration is composed from four different numbers.

days.hours.minutes.seconds

This is the best way 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 methods to find each segment of time. When you select the Duration menu, you'll find the number of minutes, seconds, hours, and years.

To aid with calculating the age in years like, for instance, it is simple to select Total Years.

It is vital to keep in mind this: the measurement is in day and then divided by the number of days, for you to get the annual value.

Rounding

At the final point, nobody claims that their age is 53.813698630136983! They simply say 53. This is rounding down. It's easy to select Rounding and then round down option from the Transform tab for it.

This will provide you with the year of birth:

After that, you are able to clean the other columns, should you want to (or this could mean because you made use of transformations within the Transform tab to prevent creating new columns) The column may be named something like Age column Age.

Things to Know

  • Refresh The age that is calculated in this manner will be updated when refreshing your data. Every time, it will be matched the date of birth to the date and moment when the data refresh was completed. It is a method can be described as an algorithm for pre-calculating age. If you need the calculation of age to be carried out dynamically using DAX In this article, I've explained the method you could use.
  • What's the reason? Power Query: Benefits of using age calculation with the Power Query is that the calculation happens when you refresh your report. The calculation is performed using an application that makes the calculation much easier and also there is no extra overhead to do it using DAX as a measure of runtime.
  • Additional scenarios This isn't an method to calculate age only by birthdate. It can be used to calculate the stock age for products as well as the difference between dates and times from one.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc of Computer engineering. He has more that twenty years' worth of experience in the area of data analysis, programming, databases, and BI development primarily using Microsoft technologies. Reza has been an official Microsoft Data Platform MVP for nine consecutive years (from 2011 to now) because of his dedication to 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 written several titles related to MS SQL BI and also is working on other books. He also was a frequent forum participant on technical forums such as MSDN and Experts-Exchange and was moderator for MSDN SQL Server Forums and holds the MCP and MCSE as well as MCITP in BI. He is the director of the New Zealand Business Intelligence users group. He is also the author of the widely popular Book Power BI from Rookie to Rock Star, which is free and contains over 17000 pages as well as Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
The speaker is 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 purpose is to help users find the most effective data solution. He is an avid Data enthusiast.This post was posted 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 posted in Power BI. You can follow any responses to this entry through the RSS feed.

Post navigation

Share visual pages on various security groups within the Power BIAge's Year Calculation that is used for Leap Year in Power BI through Power Query

Comments

Post a Comment

Popular posts from this blog

What Does Central Processing Unit (CPU) Mean?

  What Does  Central Processing Unit  (CPU) Mean? The CPU, also known as the  intral processing unit  (CPU) or processor, is the part that handles the bulk of computing in the computer.   It handles all the instructions that are that are received by the software that runs on the PC and from other  hardware  parts and acts as a powerful calculator. The CPU is put into an exact square-shaped socket on every motherboard by inserting it into its  metallic connectors (also known as pins) found on the underside.   Each socket is designed with specific pins to accommodate a particular kind of processor. cpu full form  ( central processing unit ) Because modern  CPUs produce lots of heat and are susceptible to overheating, they should be kept cool with appropriate fans or ventilation systems and protected by thermal paste. For controlling the flow of information and instructions flow between and to other parts of the computer, the CPU ...
Read more

angle-converter

Image
What is the definition of an angle ? Angles that are acute, obtuse, or narrow. An angle is a shape formed by two rays which share the same point of origin that is also known as vertex. Vertex. It is possible to inquire What are the benefits of angles? feasible to estimate the size of a building once you know how far away it is from the location you live in, as well as angles between the top and below. It is the same method is a method to determine the dimensions of the moon, or, if you've the right equipment, the circumference of our earth. If you throw something and you want to know the distance it travels, it is necessary to know the angle at which your throw is directed at. There are many other situations in which angles can be useful, but for the time being, we'll only focus on the fundamentals in geometry. The term "angle" can be divided in two dimensions. WHAT IS A FREQUENCY CONVERTER ? Frequency converter , also referred to by the name of a power fre...
Read more