Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple approach that provides a simple method for calculating the age. However, because DAX is the most used language usedin several calculationsin Power BI, many don't know about the function available in Power Query. In this blog post, I'll explain how easy the process is calculateAge with Power BI using PowerBI. This methodis very beneficial in situations where it is required to perform the age calculationcan be calculated by calculating the age on a row by row basis.

Calculate Age from a date

This is an example of the DimCustomer Table, that is part of the AdventureWorksDW table that acts as the birthdate column. I've eliminated columns that aren't required for clarity;

To calculate the age of every buyer, you need is:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; make sure to choose the first column, namely the Birthdate.
  • To add columns, click on the Column Tab and click on the Add Column Tab, which is within the "From Date & Time" section. Then, under Date, choose Age

It's that simple. it. It will calculate any variations between that column, the Birthdate column, and the current date and time.

However, the age that you see when you look into the Age column, doesn't seem like an actual age. It's because it's an actual Duration.

Duration

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

days.hours.minutes.seconds

That's what you'll discover in the details above. However, from users' standpoint, they shouldn't have to look up information as detailed in the above. There are methods you can use to find each element of the time. If you select the Duration menu you'll see that you can pull the total amount of minutes, seconds, hours, days , and even years from it.

To aid in calculating the age in years for instance it is easy to hit the Total Year:

Notice that the duration was measured in days . It was then divided into 365 days to produce the annual sum.

Rounding

It's the truth, no one states an age for their children as 53.813698630136983! They say 53, which is an rounded down. It's very easy to select Rounding and then round down by selecting the Transform tab for it.

This will give you the year-old number:

You can then tidy other columns as you'd like (or there's a possibility that you have applied transformations using the Transform tab to avoid creating new columns) You can also name this column Age.

Things to Know

  • Refresh The age that is calculated this way will get modified at the time of refreshing your database. Every time, it will match the birthdate with the date and time that the data refresh occurred. This method is a way to calculate earlier of age. If, however, you'd like the calculation to be performed dynamically, using DAX This is the way I've described the best way to use.
  • The motivation behind Power Query: Benefits of performing an age calculation with Power Query is that the calculation is carried out after you refresh the report. The calculation is performed using an algorithm that makes the calculation more easy to do, and there won't be additional work involved in doing it with DAX for a measurement of runtime.
  • Different scenarios This is not intended to be used to calculate the age of a person based on their birth date. This could be used to calculate product inventory age and the distinct dates and dates from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has an BSc with a major in Computer engineering. He has more than 20 years of experience in data analysis as well as database programming, BI and development generally making use of Microsoft technologies. He has been an Microsoft Data Platform MVP for nine consecutive years (from 2011 until now) due to his dedication to Microsoft BI. Reza is a regular blog writer, and also the co-founder and editor of RADACAD. Reza is also co-founder and the co-organizer of Difinity the conference which takes place within New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He co-authored several books on MS SQL BI and also is writing other books. He also was a regular participant in online forums for technical issues , such as MSDN and Experts-Exchange and was moderator on the MSDN SQL Server forums, and is an MCP , Microsoft Certified Specialist (MCSE) as well an MCITP in BI. He was the creator of the New Zealand Business Intelligence users group. In addition, he's also an author of the extremely popular workbook Power BI from Rookie to Rock Star, which is offered for free and features more than 17000 pages of information and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
It is an International speaker in 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 aim is to help you find the ideal solutions for data. He's a Data enthusiast.This entry was posted on the topic of 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 an excellent resource to bookmark.

Post navigation

Share various visual pages using different Security Groups in Power to use BIAge's Calculation of Years that is used for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

Random Number Generator

Random Number Generator Make use of the generator to obtain an absolutely random secure, cryptographically secure number. It generates random numbers that can be employed when the accuracy of the results is critical in shuffles of decks of cards to play an online poker game, or when drawing numbers for the lottery, raffles, or sweepstakes. How do you pick your random number from two numbers? You can use this random number generator in order to identify an authentic random number among any two numbers. For example, to get the random number that's between 10- as well as 10, put 1 into the initial input, then enter 10 in the following, then press "Get Random Number". The randomizer chooses a number between 1 and 10. random. If you want to generate a random number between 1 and 100 You can do similarly, substituting 100 as the other area of the picker. If you'd like to simulate a roll of dice the range must be between 1 and 6 for traditional dice that...
Read more

scientific and graphing calculators

Calculator Navigation and Jump to Next (Calcium)Next (Caleb)A basic calculator Calculators calculator is a gadget used to conduct calculations . Modern calculators usually include general-purpose computing systems, but they're usually constructed to complete specific functions. For example graphing calculators focus on graph-centered math such as trigonometry as well as statistics. Additionally the latest calculators are larger and are more portable than a majority of computers. However, some individual digital assistants (PDAs) are comparable in size with hand-held calculators. When calculators will be made smaller, the day will come when devices that are much smaller than credit cards can do the exact calculations that are currently used in large scientific calculators. Another alternative is similar to a handheld calculator, where the notations aren't inputted by buttons however, they are written on an LCD screen. Through this method buttons will be elimin...
Read more

digital-converter

Parts per Million by Weight in Water It's the concentration in ppm for gas found in water. This is normally expressed in weight. To measure this concentration by metric units, it is necessary to determine the water's density. will be necessary. The density of pure water must be 1000.0000 kilograms per meters 3. at temperatures of 3.98degC and the normal atmospheric pressure as of 1969. This was the first description of the kilogram. The kilo today is defined as equivalent to the mass of the prototype to allow for worldwide use for the kilogram. Water that is high-purity (VSMOW) when temperatures are 4 degC (IPTS-68) in the normal atmospheric pressure is an average density that is 999.9750 kg/m 3. . [5] Water's density is affected by pressure, temperature and impurities, i.e. the gasses dissolved in it and saltiness of water. It is alarming to note that the concentration of the gases that is dissolved in the water may affect the density it is. There is a poss...
Read more