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 main language usedin numerous computationsin Power BI, many aren't aware of this function in Power Query. In this article, we'll discuss how simple it is to calculateAge within Power BI using PowerBI. The methodis extremely helpful when the estimate of the agecan be done as an earlier calculated row by row basis.

Calculate Age from a date

Below you'll find the DimCustomer table which is part of the AdventureWorksDW table, which acts as the birthdate column. I've removed several of the extra columns so that it is easier comprehend.

To calculate your age for each client All you have to do is:

  • In Power BI Desktop, Click on Transform Data
  • In the PowerQuery Editor window; pick the Birthdate column first.
  • go to the add Column Tab click on Column Tab, and then hit"Add Column Tab" and then on "From Date & Time" section, and under Date, select the age range.

That's that. this is the method you calculate an amount that is the total of the Birthdate column, along with the current date and time.

The age, however, which appears in"under" the Age column, and doesn't seem to be an actual date. It's because it's actually a length.

Duration

Duration is a particular data type within Power Query which represents the differences between the two DateTime values. Duration is a mixture of four values:

days.hours.minutes.seconds

This is the way to take the information above into consideration. However, for the viewpoint of the user, it's not their responsibility to study the entire details of this. There are methods that could capture every single part of the period. By choosing"Durnancing", you will find that it is possible to determine the number of seconds and minutes along with days, hours and years out of it.

To assist in calculating the age in years such as, for instance you can hit Total Year:

Be aware that the duration is calculated in days . After that, it is then multiplied by days, in order to get the annual sum.

Rounding

The truth is that no one claims they are 53.813698630136983! They say 53, which is rounded down. It's simple to choose Rounding, and then round down in the Transform tab.

This will show you how old you are:

It is then possible to clean other columns, should you like (or maybe you've made use of transformations through the Transform tab, avoiding having make new columns), and call this column"Age:

Things to Know

  • Refresh The age that is calculated this way is refreshed every time you're refreshing your data. Each time, it compares the date of birth to the date and moment of the refresh. This method is pre-calculating an age. If you want the calculation to be done dynamically with DAX this is the way I explained the method you could employ.
  • The rationale behind Power Query: Benefits of doing an age calculation with Power Query is that the calculation takes place in the course of refreshing your report. It is an instrument that makes the calculation easier and faster, and there's no extra cost to calculate it using DAX to calculate the time.
  • Alternative scenarios It cannot be utilized to calculate the date of birth. It can be used to calculate stock-level age for inventory items as well as the differences between two dates or dates 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 on Computer engineering. He has more than 20 years old. experience in the field of data analysis and database programming, BI and development with a focus on Microsoft technologies. He is an official Microsoft Data Platform MVP for nine years consecutively (from 2011, until now) because of his love for Microsoft BI. Reza is an experienced blog writer and co-founder and the editor for RADACAD. Reza is also co-founder and coorganizer of Difinity Conference. 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 wrote several books on MS SQL BI and also is working on additional books. He was also a frequent participant in online forums dealing with technical issues such as MSDN and Experts-Exchange and was the moderator of the MSDN SQL Server forums, and holds an MCP and MCSE as well as 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 1800 pages of material and 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 dream is to help users find the most effective data solution. He is a Data enthusiast.This article was written by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is filed with Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. The following entry was filed under Power BI.

Post navigation

- Share Multiple Visual Pages by using Different Security Groups in Power BIAge's Years Calculation that can be used for Leap Year in Power BI through Power Query

Comments

Post a Comment

Popular posts from this blog

Calorie Calculator

Which most important? calories and kilocalories ? "Current "calorie" that we use in our food in reality Kilocalorie. 1 (1) Kilocalorie is equivalent to one (1) Calorie (uppercase C). Kilocalorie is the quantity of heat required to raise the temperature of a kilogram of water by one degree Celsius. How do you know what is caloric value of food calculated? It's possible that it's the result of study of how they lead in weight gains, but it's not the case. But it's not so. Avery wonderful question that should be asked as the majority of people are unaware of the significance of calories . In the first place , a calorie is not something that can be said fill or be empty. It is not feasible to add calories in a bottle. It's impossible to store calories in bottles. calorie is a measurement unit that is used for energy measurement. It's the amount required to raise the temperature of an mL, (which equals one Gram) of water by an inch Cels...
Read more

Scientific Calculator

What Is A Scientific Calculator Calculators with electronic circuits are around for centuries, and have become more and more sophisticated. But the first calculator with a scientific design was created and released to the public within the last decade, back in 1968. These devices are now seen in classrooms across the world, but what are they exactly? In addition, how are they different from standard calculators? I've described the history of the tool and uses, as well as special functions and the difference between regular and scientific calculators in this piece to help you get started making use of the tool. What is a Scientific Calculator ? Scientific calculators are digital calculators equipped with functions to solve science, engineering, and mathematics problems. These calculators enable users to solve trigonometry issues using embedded trigonometric, inverse, or hyperbolic calculations. Users can also work with degrees, radians, as well as Grads by s...
Read more

What is the Full Form of SIT?

  Composition of SIT The court decides on SIT and it is tasked to assist and collect  information. of evidence.   This is a significant difference in comparison to CBI in this regard.   SIT is a temporary solution. SIT is a temporary measure It is not a permanent solution. SIT is over when the case is settled. It is comprised of highly respected  police officers that are police officers from the  police force .   The departmental head of the police force across the state, eminent ex-police officers, as well as civil servants usually are assigned to collect evidence for an investigation. The types of cases that can be assigned to the SIT(  sit full form ) There are many instances that could be handed over to SIT.   These could be cases of a high profile nature and generally being the case, in cases where the local police department was unable to reach an agreement. Objectives of SIT The sole purpose of SIT consisted of conducting free and fair...
Read more