Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way that can be used for calculating the age. But, as DAX is the primary language usedin numerous analysesin Power BI, many do not realize this option within Power Query. In this blog article, I am going to illustrate how easy it can be to calculateAge in Power BI using Power BBI. The methodis extraordinarily helpful when you have in order to calculate the estimated age.can be accomplished on an earlier calculated row-by-row row basis.

Calculate Age from a date

Here's the DimCustomer table, which belongs to the AdventureWorksDW table that is the column for birthdates. I've removed the additional columns so that it is easier to read.

If you're looking to calculate an age range for every client, the only thing you need is to:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; choose the first column, Birthdate.
  • Click on The Add Column Tab and then click on the "From Date & Time" section, and under Date select the appropriate age range.

This is all there is to it. This is the method you calculate any variation between the Birthdate column, in addition to the current date and date.

However, the age appears in"Age", but the Age column, however, it does not appear to be a real age. That is because it is an actual duration.

Duration

Duration is a distinct type of data kind of the type 'data' in Power Query which represents the distinction between the two DateTime values. Duration is made up of four numbers:

days.hours.minutes.seconds

and that is how you read the information above. But from a user's perspective, don't believe that they'll be able to understand specifics such as the ones listed above. There are different ways to make each section of time. If you click on the Duration menu you will see that you can determine the amount of seconds and minutes and hours, days, and even years from it.

For assistance with calculating the age in years for instance , just click the Total Year:

Be aware that the duration is calculated using days. Then it is multiplied by days to give you the yearly value.

Rounding

In the end, nobody claims to be 53.813698630136983! The number is 53. then round down. It's simple to select Rounding and then round down on your Transform tab.

This will provide you with the years of age:

Then , you can clean up the other columns you'd like to (or it could be that you've utilized transforms within the Transform tab to avoid creating new columns.) This column can be named. column: Age.

Things to Know

  • Refresh The age that is calculated by this method will be updated every time refresh of your data. And each time, it will match the birth date to the time and date at the time of refresh. This method is an earlier calculation of the age. If you require the calculation be made dynamically using DAX here's how I explained the procedure to use.
  • What's the explanation? Power Query Benefits of using the feature for age calculation in Power Query includes that it is executed each time you refresh your report. The calculation is carried out using an algorithm that makes the calculation simpler and quicker, and there's no extra expense to calculate it with DAX for a measurement of of runtime.
  • Alternative scenarios It's not in the calculation of age just from the birthdate. This can be used to calculate the age of inventory in the case of products as well as the difference between two dates or times one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He earned an BSc in Computer engineering. He has more than twenty years' expertise in data analysis database, BI, programming and development, mostly using Microsoft technologies. He is a recognized Microsoft Data Platform MVP for nine consecutive years (from 2011 until the present) due to his commitment to Microsoft BI. Reza is a prolific journalist , and is co-founder for RADACAD. Reza is also co-founder as well as coordinator of Difinity event as well as the Difinity conference and Difinity conference 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 on MS SQL BI and also is writing a few additional. He is also a frequent member of online forums for technical questions like MSDN and Experts-Exchange and was also the moderator on MSDN SQL Server forums, and is a holder of an MCP, MCSE, and the MCITP for Business Intelligence. He is the Director of the New Zealand Business Intelligence users group. Also, he's the co-author of the highly acclaimed volume Power BI from Rookie to Rock Star, which is free and contains nearly 700 pages worth of information in addition to The Power BI Pro Architecture published by Apress.
This speaker has been an International Speaker of 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 main goal is to help users locate the best information solution. He is Data enthusiast.This post was categorized in Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great guide to bookmark.

Post navigation

Share various visual pages by using different Security Gruppen in Power BI's BIAge's years calculation that is used to calculate Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

Random Number Generator

Random Number Generator Utilize this generator to obtain an absolute randomly digitally secure number. It generates random numbers that can be used in situations where precision of the result is important for example, such as when shuffling deck of cards in games of Poker as well as drawing numbers to win sweepstakes, raffles, or giveaways. What's the best method to select the best random number between two numbers? You can use this random number generator for you to generate an authentic random number from any two numbers. For instance, to get a random number within the range of 1 to 10 including 10, you'll need to input 1 first in the input field and 10 in the second field then click "Get Random Number". Our randomizer will select one of the numbers from 1 to 10 randomly. To generate the random number between 1 and 100, apply similar methods with 100, but it's in the 2nd field of the randomizer. For the purpose of creating the illusion of rol...
Read more

best calculator for school

This is the best calculator for school 2021 We break down one of the best calculators for school in relation to function. A calculator is one of several back-to-school essentials you'll find on your classroom's shopping list each year. During the middle school and early high school years, all you may need is the basic calculator. But as science and math become more complicated, you could require calculators at school to perform multiple operations. There are several types of calculators to pick from, which could cause confusion regarding which type to purchase. When calculators become more complex and sophisticated, they're usually more expensive. You might be wondering if you should spend more than $50 for one, especially when some calculators are for sale at less than $10. This guide will explain the various kinds of calculators and guide you to which are best calculators for school. Guide to Buyers Calculators are available in different shapes and...
Read more

Is Scientific Calculators still up and running? [closed]

Is Scientific Calculators still up and running? [closed] I've been a civil/structural engineer for over 20 years. My HP 48GX recently completed its last calculation . So I started looking for an alternative and it seems it's the case that sales for graphing/scientific calculators is changing. My question: Are smartphones becoming the replacement for calculators in the world of engineering and all one needs to do is purchase a " graphing calculator " application? (I am not referring to the open sourced HP48 app)engineering-generalShareImprove this questionFollowedited Dec 19 '19 at 6:25slm 103 44 bronze badgesasked Feb 23 '17 at 7:39 SlydeRule 1,206 11 gold badge1111 silver badges2222 bronze badges There is a good chance they'll still sell many of the older graphing calculators (I've still seen TI-84s that sell for around $150 CDN). In the light of this, since you've mentioned it, I'm not sure why there would be much of a mark...
Read more