Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method that is a quick and easy method in calculating the age. However, as DAX is the largest and most well-known language usedin several calculationsin Power BI, a lot of people do not know about the functions available in Power Query. In this blog article I'll detail how simple it can be to calculateAge for PowerBI using PowerBI. This methodis very beneficial for situations when it is necessary to carry out the age calculationcan be done from an earlier calculated row-by-row basis.

Calculate Age from a date

This is one of DimCustomer Table, that is from the AdventureWorksDW table that acts as the column for birth dates. I've eliminated columns that weren't needed to make it more readable;

For you to calculate the age of each client, you'll need:

  • In Power BI Desktop, Click on Transform Data
  • In the PowerQuery Editor window, ensure to select the Birthdate column first.
  • Select the Add Column Tab and click on the Add Column Tab, which is beneath the "From Date & Time" section. And under Date, select Age

This is all there is to it. This is how you calculate any differences from both the Birthdate column, as well as the current time and date.

But, the age you can see when looking through the Age column, does not appear like an age. It's because it's not a duration.

Duration

Duration is a specific type of data that is utilized in Power Query which represents the differences between the two DateTime values. Duration can be described as a mixture of four different values:

days.hours.minutes.seconds

and that's what you will find in the above information. However, from a users' standpoint, they shouldn't have to read information as detailed in the above. There are methods to locate every component of time. If you select the Duration menu you'll be able to see how you can extract the amount of minutes, seconds, hours, weeks and years from it.

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

Be aware that the duration of the event is measured in days . It was later divided in 365 hours for the year to get the total.

Rounding

It's the truth, nobody declares that their kid's age is 53.813698630136983! They use 53 which is reduced to. It's easy to choose the Rounding, then click round down by selecting the Transform tab.

This will give you the years of experience:

Then, you can cleanse other columns if you'd like (or it is possible that you have applied transformations in the Transform tab in order to not create new columns) And then name this column: Age.

Things to Know

  • Refresh The age calculated this way will be altered when refreshing your database. Every subsequent time, it'll be matched to the birthdate with the date and time that the refresh was made. This method is an earlier calculation of age. If, however, you would like the calculation of age to be dynamically performed by using DAX This is the approach I've shown how to use.
  • The reason behind Power Query: Benefits of performing an age calculation with Power Query is that the calculation happens each time you refresh your report. It's done with an algorithm which makes the calculation easy to do, and there won't be extra work to calculate it with DAX for a measurement of runtime.
  • Different scenarios. This isn't intended for the calculation of the age of a person based on their birth date. This could be used in conjunction with product inventory and distinct 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 earned a BSc from Computer engineering. He has more than 20 years of expertise in data analysis, databases, BI, programming and development generally utilising Microsoft technologies. He was an Microsoft Data Platform MVP for nine years in a row (from 2011 until the present) for his commitment to Microsoft BI. Reza is a regular blog author, and is the founder and the editor for RADACAD. Reza is co-founder and coordinator of Difinity the conference which takes place 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 has written a number of books about MS SQL BI and also is working on other books. He also was a regular forum participant on online forums for technical issues , such as MSDN and Experts-Exchange and was moderator of MSDN SQL Server forums, and holds an MCP and MSSE as well as an MCITP in Business Intelligence. He was the creator of the New Zealand Business Intelligence users group. Additionally, he's the author of the very popular books Power BI from Rookie to Rock Star, which is available for download for free and includes more than 17000 pages of information and The Power BI Pro Architecture published by Apress.
It is an International speaker on 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 desire is to assist you discover the right solutions to data issues, and he's an avid Data enthusiast.This post was published under Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. This is a great source to bookmark.

Post navigation

Share Different Visual Pages with Different Security Groups in Power the BIAge's Calculation of Years that is used for Leap Year in Power BI by making use of Power Query

Comments

Post a Comment

Popular posts from this blog

scientific and graphing calculators

Calculator Navigation (Calcium) Jump to Next (Calcium)Next (Caleb)A Simple calculator Calculators calculator is a device for conducting calculations . Modern calculators generally include general-purpose computing devices, however, they're typically specifically designed to accomplish specific tasks. For example graphing calculators focus on graph-centered math such as trigonometry and algebra and statistics. Also the latest calculators are bigger and more portable than majority of computers, however certain individual digital assistants (PDAs) are comparable in size with hand-held calculators. When calculators become made smaller, the day will come that devices larger than credit cards will be able to accomplish the same calculations that are currently used in large scientific calculators. Another alternative is similar to a handheld calculator which is where the numbers aren't entered via buttons, but instead are written on the screen. In this way, the requ...
Read more

Random Number Generator

Random Number Generator Make use of this generator to get a trully random and cryptographically safe number. It generates random numbers that can be utilized when accuracy of the result is important for instance, when shuffling cards for games of poker, or drawing numbers for raffles, lottery or sweepstakes. How do I pick an random number from two numbers? You can use this random number generator to select a truly random number from any two numbers. To get, for instance, a random number between 1-10 with 10, you must enter 1 first followed by 10 in the second after which you click "Get Random Number". Our randomizer picks the number 1-10 at random. In order to generate random numbers, choose the random number between 1 and 100, you can do the same however, using 100 within two fields on the selector. If you're looking to simulate a dice roll, the number should range from 1 - 6, to simulate a typical six-sided dash. If you want to generate many uni...
Read more

Conor McGregor

Conor McGregor Net Worth, Biography and Career, Family, Physical appearance will be discussed here. The official date of birth is 14 July 1988. He has a age of 33 right today. His birth sign is, Cancer that he hails from Crumlin in Dublin, Ireland. McGregor can be described as Conor Anthony in full. Conor McGregor's father is Conor McGregor is Tony McGregor, and the mother of Conor McGregor is Margaret McGregor. Conor McGregor, 26, also has two sisters in the shape of Erin McGregor and Aoife McGregor who are both singers. Conor was born in Ireland and went to Irish-language schools throughout his education. Conor attended Gaelscoil Scoil Mologaschool, which is located within Harold's Cross, for his primary education. Conor McGregor Conor was a member of the Lourdes Celtic Football Club used to be Conor's home as a youngster. Conor started boxing at the age of 12 years old at Crumlin Boxing Club. In defense of himself and increasing his confidence were ...
Read more