Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, because DAX is the most commonly used language usedin numerous calculationsin Power BI, many are unaware of the functions available in Power Query. In this blog article I'll go over how simple it is to calculateAge in Power BI in conjunction with PowerBI. The methodis highly useful when you need to calculate your estimation of your agecan be carried out in a pre-calculated row rows.

Calculate Age from a date

This is the DimCustomer table from the AdventureWorksDW table that functions in the role of the date column. I've eliminated columns that aren't required for ease of comprehend.

For you to calculate your age for each person the only thing you'll need is to:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; start by selecting the column titled Birthdate.
  • Go to the Add Column Tab, follow"Add Column." Then, click the "From Date & Time" section. In the Date, select the age range.

It's that simple. this does calculate any change from the Birthdate column as well as the current date and time.

However, the age that shows up in the Age column, does not actually look like an age. It is due to the fact that it is the term "Duration.

Duration

Duration is a specific kind of data type that is used in data type in Power Query which demonstrates the distinctions between the two DateTime values. Duration is made up from four figures:

days.hours.minutes.seconds

This is the method to look up the values above. For the users' perspectives, they shouldn't be expected to look up details like this. There are methods that can determine each part that corresponds to in the time period. When you select the Duration menu, you'll discover that you can calculate the amount of seconds, minutes, hours days, and years.

For calculating the age in years for instance, it's easy to find Total Year:

The duration is measured in days , and then after that , it is divided by number of days, you will get the annual value.

Rounding

There is no truth to it, and nobody declares one's age was 53.813698630136983! They use the term 53 and then they round it down. Select Rounding and then round down in the Transform tab.

This will give you the number in years:

Clean out the other columns If you wish (or this could mean that you made use of transformations by using the Transform tab to not create new columns) You can name this column"Age:

Things to Know

  • Refresh The date calculated in this method will be updated at the time of refreshing your dataset. Each time, the system will evaluate dates of birth along with the dates and times for refreshing. This method is the initial calculation of the age. If you require the calculation of age executed dynamically, using DAX This is the way I've explained in detail how to utilize.
  • What is Power Query: Benefits of making age calculations with Power Query is that the calculation takes place at the moment of refreshing your report. It refreshes the report with a tool that makes calculation more simple, and there is no additional cost to doing it with DAX, which is a measure time.
  • Another reason is that it's not meant to calculate age solely based on birthdate. This could be used to calculate inventory of products as well as the differences between two dates, or times, from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has earned a BSc of Computer engineering. He holds more than 20 years of expertise in data analysis as well as BI, databases programming, and development predominantly on Microsoft technologies. He was a Microsoft Data Platform MVP for nine years in a row (from 2011 until the present) because of his dedication for Microsoft BI. Reza is a regular blog writer, as well as the editor and co-founder of RADACAD. Reza is also co-founder as well as co-organizer for the Difinity event located at New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing other books. He was also a regular participant in forums online for technical issues such as MSDN and Experts-Exchange, and was the moderator for MSDN SQL Server forums, and is an MCP as well as Microsoft Certified Specialist (MCSE) as well an MSCITP of Business Intelligence. He is the head of the New Zealand Business Intelligence users group. He is also the editor of the book that is very well-loved Power BI from Rookie to Rock Star The book is completely free and includes over 1700 pages of content as well as a book titled Power BI Pro Architecture published by Apress.
His credentials consist of being an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, and SQL users groups. And He is a Microsoft Certified Trainer.
Reza's main goal is helping users find the right data solution. He is a Data enthusiast.This entry was published on the topic of Power BI, Power BI from Rookie to Rockstar, Power Query and is covered within Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. Please leave a comment.

Post navigation

- Share Different Visual Pages with different Security Groups Power BIAge's Years Calculation that is used for Leap Year in Power BI using Power Query

Comments

Post a Comment

Popular posts from this blog

What is the full form of computer computer full form Computer  is  not an acronym , it is a word derived from a word "compute" which means to calculate. So, in simple words you can say that computer is an electronic device which is used for fast calculation. Some people say that COMPUTER stands for  Common Operating Machine Purposely Used for Technological and Educational Research . It is only a myth because first this definition does not make any sense and second when the computer fuul form were invented they were just calculating machines which needs a lot of space for establishment.  "A computer full form is a general purpose electronic device that is used to perform arithmetic and logical operations automatically. A computer consists of a central processing unit and some form of memory." COMPUTER = Arithmetical Logical Unit (ALU) +  Control Unit (CU)   ALU : Arithmetic Logical Unit is used ...
Read more

illuminance-converter

Image
What is the definition of an angle ? Angles which are sharp, acute, or narrow. An angle is the shape caused by two rays, which share the same point of origin and is referred to as the vertex. Vertex. The question is: what are the advantages of angles? It's possible to determine the size of a tower when you know the distance from you it is and also the angle the top of the tower and the ground. With this method, you'll be able to figure out the size of the moon is, or, if you have the right tools, the diameter of our globe. If you're throwing something and you want to know the distance it goes, you need to know the angle the object is thrown from. There are many more applications where angles could be beneficial, but at this point we'll only focus on the fundamentals in geometry. Angles are classified in terms of their size: WHAT IS A FREQUENCY CONVERTER ? A frequency converter, often is referred to as a frequency converter . It is a device that absorbs the po...
Read more

angle-converter

Image
What is the meaning for the word "angle" ? The angle is acute, narrow, or narrow. An angle is an shape formed by two rays, which share an identical starting point known as the vertex . It is a matter of what are the benefits of angles? It is possible to calculate the size of a tower when you know the distance it is from you and angles between the top and the surface as well as the top. By employing the same method using the same method it is possible to determine the moon's diameter or in the event that you have the right apparatus and instruments, the size of our planet. Also, if you throw something and want to know the distance it travels it is necessary to determine the angle that the object is thrown at. There are several applications where angles are useful however, for the time being, let's look at the fundamentals of geometry. Angles can be classified based on their dimensions: WHAT IS A FREQUENCY CONVERTER ? Frequency converters, commonly called po...
Read more