Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, as DAX is among the commonly used language usedin numerous calculationsin Power BI, many do not know about this feature of Power Query. In this blog post I will show how easy it is to calculateAge within Power BI and Power BI. This methodis exceptionally beneficial when you have the ability to calculate the calculation of age.can be carried out using a calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table, which is an element of the AdventureWorksDW table which contains 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 approximate age of each client, the only thing you need to do is to:

  • In Power BI Desktop, Click on Transform Data
  • In the PowerQuery Editor window, select the column that is the primary one, Birthdate.
  • To add a column, click on the Column Tab and then click on the "From Date & Time" section, and then under Date Choose the appropriate age range.

That's about it. This is how you calculate the ratio between the birthdate column, aswell as the current date and time.

But, the age appears on the Age column, but it doesn't appear to be an age. This is because it's an actual duration.

Duration

Duration is a specific type of data that is used in Power Query which represents the differentiating factors between the two DateTime values. Duration is a combination of 4 numbers

days.hours.minutes.seconds

This is the way to understand the above numbers. From the point of view of the user, it's not required of them to be able to discern the meaning of these numbers. There are ways to determine each part of the time. By selecting the Duration menu you'll get the total of seconds, minutes, hours months and days from it.

to assist to assist calculating the age in years like, for instance, it is simple to choose Total Years.

It is important to understand how the length is calculated in days, and after that divided by the number of days, to give you the annual figure.

Rounding

As a final note, nobody declares that they are 53.813698630136983! They declare 53. is the number rounded down. It's easy to choose Rounding and then choose the round down option in the Transform tab for it.

This will give you the year of birth:

Then, you can clean the other columns, in the event that you'd like (or perhaps it's because you applied transformations to the Transform tab to prevent the creation of new columns) The column may be named something like Age the column.

Things to Know

  • Refresh The age that is calculated in this manner will be updated whenever you refresh your data. Each time, it will be matched the date of birth with the date and moment when the data refresh was made. The method is an algorithm used for pre-calculating the age. If, however, you need the calculation of the age to be done dynamically using DAX Here I have explained the method you could make use of.
  • What's the cause? Power Query The benefit of using age calculation using Microsoft Power Query is that the calculation takes place when you refresh your report. The calculation is performed using an application which makes the calculation simpler to do, and there's no extra overhead to do it using DAX as a measure of the runtime.
  • Other scenarios It isn't an method to calculate the age of a product solely based on birth date. This can be used in order to calculate the time of inventory for items and also the distinctions 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 a BSc of Computer Engineering. He is more than twenty years' worth of experience in the area of data analysis, database programming, BI and development mostly using Microsoft technologies. He was an official Microsoft Data Platform MVP for nine consecutive years (from 2011 to now) for his dedication toward Microsoft BI. Reza has a reputation for being a prolific writer and is co-founder with RADACAD. Reza is also the co-founder as well as 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 books about MS SQL BI and also is writing other books. He also was a frequent user of forums for technical issues like MSDN and Experts-Exchange and was moderator for MSDN SQL Server Forums, and holds the MCP and MCSE , as and the MCITP for Business Intelligence. He is also the head of the New Zealand Business Intelligence users group. He is also the author of the widely acclaimed work Power BI from Rookie to Rock Star, which is free and offers 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, along with SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help you to find the most effective data solution. He is an avid Data enthusiast.This post was filed under 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 posted in Power BI. Bookmark the permalink.

Post navigation

Use different visual pages to share using different security groups in PowerBIAge'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

volumeFlowRate-converter

Image
What is an angle ? Acute, obtuse angles An angle is a form created by two rays that share an origin point, referred to as vertex. vertex. One might wonder what are the benefits of angles? You can calculate the building's height if you know its distance from your location, as well as angles between the top on the tower as well as ground. With the same method you can calculate how big the moon is, or, with the appropriate tools, determine the circumference of our planet. Additionally, if you throw something, and you wish to determine the distance it will travel, you'll need to be aware of the angle you throw it at. There are many other fields that require angles however in the present 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 convertor, also called a power frequency convertor, is an electronic device that takes power from the source, typically at 60 or 50 Hz, and...
Read more

reactiveEnergy-converter

Image
What's an angle ? It is a term used to describe angles which are acute, obstrusive, or narrow. An angle is a form made by two rays that have the exact same beginning point which is referred to as vertex. vertex. One might ask: What are the advantages of angles? possible to calculate the height of a tower when you know the distance it is from your location and the angle between and the summit of the tower and the ground. By using this exact technique it is possible to estimate how big the moon is or, if equipped with the right tools to do so, the total circumference of our world. If you throw something and you'd like to know how far that it travels, you have to determine the angle at which the object is being thrown at. There are many other areas in which angles are beneficial, but for the moment 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 referred to...
Read more

flowers name |10 Flowers Name In Hindi-English| Name Of Flowers List | flowers name in hindi

  flowers name  |10 Flowers Name In Hindi-English| Name Of Flowers List | flowers name in hindi 10 flowers name In english 1.   Heather Welcome your little Heather into the world of the living and watch her grow as this beautiful purple plant.   In Scotland, this plant grew in barren lands, bringing life to the rocky grounds.ADVERTISEMENT 2.   Holly "Are you a Christmas baby?" was the question asked by everyone who's ever met an Holly.   Holly bushes have punchy red berries, making them immediately  recognizable  they are very popular during the Christmas season.   The name originates directly from the Old English word "holegn," which means "to prick." 3.   Ivy In Ancient Greece,  newlyweds  were given an ivy-worshipping wreath to signify their faithfulness to one another.   Today, the Old English name still represents faith.   We're sure your lovely Ivy will be as  cool and stylish as Blue Ivy Carter . 4.  ...
Read more