Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method for calculating the age. However, as DAX is the primary language usedin numerous calculationsin Power BI, many don't know about this function within Power Query. In this article, I'll demonstrate how simple it is to calculateAge for PowerBI using PowerBI. This methodis very beneficial in situations where your estimation of the agecan be done as an earlier calculated row-by-row row basis.

Calculate Age from a date

Below, you will see the DimCustomer table, which is a one of AdventureWorksDW table, which is"the birthday date column. I've removed a few the extra columns for easier understand.

In order to calculate your age for each customer all you have to do is:

  • In Power BI Desktop, Click on Transform Data
  • In the PowerQuery Editor window; pick the Birthdate column first.
  • click on the Add Column Tab after which hit"Add Column Tab," then click on the "From Date & Time" section. Under Date, choose Age

That's it. This is the method you calculate an amount that is the total of the Birthdate column, together with the date and time of the present.

However, the date, which is displayed under the Age column, but doesn't appear to be an actual date. This is because it's a real duration.

Duration

Duration is a unique form of data that is part of Power Query which represents the differences between the two DateTime values. duration is a combination of four values:

days.hours.minutes.seconds

and that is how you can look at the data. From the perspective of the user it is not the responsibility of them to study the specifics of this. There are methods to extract every bit of information from the period. If you choose"Durnancing" in the Menu, you will see that you are able to take the number of minutes and seconds or hours, days, and years from it.

For assistance in calculating the age in years like, for instance you can hit Total Year:

It is important to note that the duration is measured in days . Then it is divide by the total number of days, to yield the yearly amount.

Rounding

In reality, no person declares that they are 53.813698630136983! They state 53, which is reduced to a round number. It's simple to choose Rounding and round down from the Transform tab.

This will give you an indication of your age in years:

You can then clean the other columns, if you wish (or maybe you've made use of transformations by using the Transform tab, avoiding having inventing new columns), and call this column; Age:

Things to Know

  • Refresh The age that is calculated this way will be refreshed every time you're refreshing your information. Each time, it will compare the birthdate to the date and duration at the time of refresh. This method is pre-calculating an age. If, however, you require the calculation be dynamically done using DAX this is the way I explained the method that you can use.
  • The reason for Power Query: Benefits of performing an age calculation with Power Query is that the calculation is made in the course of refreshing your report, using an instrument which makes the calculation simpler and faster, plus there is no additional overhead in calculating it using the DAX method to determine runtime.
  • Alternative scenarios It can't be utilized to calculate the date of birth. This can be used to calculate an inventory level age calculation and the differences between two dates and dates 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 in Computer engineering; he is more than twenty years' experience in the field of data analysis and databases, BI and development with a focus on Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years running (from 2011, until now) because of his love of Microsoft BI. Reza is a prolific blog writer who is also the co-founder as well as the editor for RADACAD. Reza is also co-founder of 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 books on MS SQL BI and also is working on more books. He also was a regular participant in forums online for technical issues like MSDN and Experts-Exchange as well as moderator of the MSDN SQL Server forums, and is an MCP and MCSE and an MCITP of Business Intelligence. He is also the leader of the New Zealand Business Intelligence users group. The group also is writer of the highly acclaimed book Power BI from Rookie to Rock Star, which is free with more than 170 pages of content. It is also the author of 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 users groups. And He is a Microsoft Certified Trainer.
Reza's main goal is helping users find the best data solution. He is a Data enthusiast.This piece was written by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is filed into Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

- Share Multiple Visual Pages with different security groups within Power BIAge's Years Calculation which works for Leap Year in Power BI using Power Query

Comments

Post a Comment

Popular posts from this blog

random number generator

random ( random)Generate pseudo-random numbers Original code Lib/random.py The module implements pseudoIt implements pseudo random number generators for various distributions. For integers, there is uniform selection of an array. For sequences, there is a uniform selection of the random element as well as it is a method to generate a random permutation of a list in-place, and a program for random sampling without replacing. On the real line, there are tools that compute normal, uniform (Gaussian) Lognormal, Negative exponential, gamma, and beta distributions. For the purpose of generating distributions for angles such as it is possible to use the von Mises distribution is available. Almost all module functions depend on the basic function random() that generates a random float evenly across the semi-open range [0.0, 1.0). Python utilizes to use the Mersenne Twister as the core generator . It produces precision floats of 53 bits and runs for 2**19937-1. The implemen...
Read more

KGF Full Form What is the full format of KGF?

  KGF Full Form  What is the full format of KGF? It is the gold mining zone of Bangarpet Taluk, Kolar District located in Karnataka.   It is the biggest Indian gold mine. However, it was closed in 2001 due a rise in manufacturing costs and lower output, and the absence of gold deposits. It was the second-deepest gold mine in the world and was renowned for its history of gold mining.   It's also called "Little England" because of the stunning views it offers and desert climate.   The region is still home to English bungalows and well-planned street.   Kolar is believed to date from the second century AD and is older than Bangalore.   Ganga created Kolar as her capital and also had her own city, Mysore as well as Coimbatore. The story of Kolar Gold Fields|  full form of kgf K  The olar Gold Fields  (KGF), also known as 'Little British' can be found in South Indian Kolar District, Karnataka.   It is located approximately 100 kilometers...
Read more

scientific calculator

Online Scientific Calculator This scientific calculator, which we offer online absolutely free of charge and is capable of performing the"normal" operations "normal" operations, also has the particular advantage of offering a variety of other options, for example, solving second and first degree equations, converting various units of measurement, performing operations with percentages, work with complex numbers, automatically calculate integrals and more. More than just a basic scientific calculator, this tiny online calculator permits operations to be introduced using conventional mathematical notation. In this way, the perception of the operation to be calculated is facilitated and the possibility of introducing unwanted errors is reduced.https://web2.0calc.com/widgets/horizontal/ For instance, try entering the following calculations : Simple arithmetic expression: First degree equations: Second degree equations: Calculation u...
Read more