Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, since DAX is among the used language usedin numerous calculationsin Power BI, many do not realize this function in Power Query. In this blog post , I will explain how simple you can calculateAge in PowerBI using PowerBI. This methodis very helpful in situations where you need for calculate the age calculationscan be done on a row-by row basis.

Calculate Age from a date

Here's the DimCustomer table that's part of the AdventureWorksDW table, which is the birthdate column. I've removed some of the columns that aren't necessary in order to make it more user-friendly;

To calculate the average age of every client must be, all you must do is to:

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

That's all there is to it. This is how you calculate the ratio between Birthdate column, as well being the current date and time.

The age displayed in the Age column doesn't actually appear to be an actual age. This is due to its actual duration.

Duration

Duration is one of the types of data used for Power Query which represents the differences in two DateTime values. Duration is a mixture of 4 numbers

days.hours.minutes.seconds

This is the way to look up the values above. However, from the perspective of the user it is not the norm for them to understand the specifics of this. There are ways to determine each part of the time. If you click on the Duration menu you'll be able to see the number of minutes, seconds, hours, and years.

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

It is important to understand how the length is calculated in days and after that divided by number of days to provide you with the annual amount.

Rounding

As a final note, nobody states that you are 53.813698630136983! They simply say 53. This is rounded down. It's easy to select Rounding and then round down option from the Transform tab for it.

This will provide you with the age of the years:

You can then clean the other columns If you'd like (or the reason could be because you made use of transformations within the Transform tab to prevent the creation of new columns) The column could be named as Age: column Age.

Things to Know

  • Refresh The age that is calculated in this way will be updated every time you refresh your data. Every time, it will match the birth date with the date and the moment when the data refresh took place. The method involves an algorithm for pre-calculating age. If, however, you want the calculation of age to be carried out dynamically using DAX Here I have explained the method which you can utilize.
  • Why is this? Power Query Benefits of using age calculation in The benefit of using age calculation with Query is that the calculation is carried out at the time of refresh of your report. It's done with an application that facilitates the calculation as well as there is no extra cost to calculate it using DAX as a measure of runtime.
  • Additional scenarios This is not an method to calculate age just by birthdate. This method can be used to calculate the age of inventory for products and also to determine 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 has a BSc of Computer Engineering. He has more that twenty years' experience in the area of data analysis, programming, databases, and BI development primarily using Microsoft technologies. He has been an official Microsoft Data Platform MVP for nine years (from 2011 until today) because of his dedication towards Microsoft BI. Reza is known as a prolific writer and co-founder of 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 a few publications on MS SQL BI and also is working on other books. He was also a regular forum participant on technical forums such as MSDN and Experts-Exchange and was moderator on the MSDN SQL Server forum and holds the MCP and MCSE as well as an MCITP in BI. He is the head for the New Zealand Business Intelligence users group. He is also creator of the highly popular work Power BI from Rookie to Rock Star, which is free and contains more than 17000 pages as well as 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 User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help you discover the most effective data solution. He is a Data enthusiast.This article was published in 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. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

Share different visual pages using various security groups within PowerBIAge's Year Calculation that is used for Leap Year in Power BI through Power Query

Comments

Post a Comment

Popular posts from this blog

  shiv chalisa , shiv chalisa lyrics Jai ganesh girija suvan Mangal mul soojan Kahat ayodhya das tum deu Abhaya varadan Jai girija pati dinadayala Sada karat santan pratipala Bhala chandrama sohat nike Kanan kundal nagaphani ke Anga gaur shira ganga bahaye Mundamala tan char lagaye Vastra khala baghambar sohain Chhavi ko dekha naga muni mohain Maina matu ki havai dulari Vama anga sohat chhavi nyari Kara trishul sohat chhavi Bhari karat sada shatrun chhayakari Nandi ganesh sohain tahan Kaise sagar madhya kamal hain jaise Kartik shyam aur gana rauo Ya chhavi ko kahi jata na kauo Devan jabahi jaya pukara Tabahi dukha prabhu aap nivara Kiya upadrav tarak bhari Devan sab mili tumahi juhari Turata shadanana apa pathayau Lav nimesh mahi mari girayau Aap jalandhara asura sanhara Suyash tumhara vidit sansara Tripurasur san yudha machai Sabhi kripakar leen bachai Kiya ...
Read more

volumeFlowRate-converter

Image
What is an angle ? Obtuse, acute angles An angle is the shape made by two rays which share the beginning point, known as vertex. vertex. Many might be asking what are the benefits of angles? You can determine the building's height if you know its distance from you, as well as angles between the top of the building and the ground. Utilizing the same technique , you can determine how big the moon is and, if you have the right tools, the radius of our own planet. If you throw something and wish to determine how far it'll travel, you'll need be aware of the angle that you throw it. There are many other fields that require angles . However, for the time being we'll look at the basics of geometry. Angles can be divided by their size: WHAT IS A FREQUENCY CONVERTER ? A frequency converter, sometimes referred to as a power frequency converter is a device that accepts power from the source, typically at 50 or 60 Hz, and converts it into 400 Hz power output. There are m...
Read more

volumeFlowRate-converter

Image
What is an angle ? Acute, obtuse angles An angle is a shape formed by two rays sharing the same starting point, also known as vertex. vertex. Some might ask what are the advantages of angles? You can calculate the height of a tower by knowing its distance from you, and also the angles between the top of the building and ground. With the same methods you can calculate how big the moon is or, using the appropriate tools, determine the radius of our own planet. Additionally, if you throw something and want to know how far it'll be able to travel, it's important to be aware of the angle that you throw it at. 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 according to their dimensions: WHAT IS A FREQUENCY CONVERTER ? A frequency converter, also known as a power frequency convertor, is a gadget that accepts power from the source, typically at a frequen...
Read more