Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, because DAX is one of the widely used language usedin several functionsin Power BI, many do not understand the functionality available in Power Query. In this article I will describe how simple it is to calculateAge within Power BI with Power BI. This methodis extremely beneficial for situations where the calculation of the agecan be performed on pre-calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table that is included in the AdventureWorksDW table, which is an age column. I've removed a few of the additional columns to make it easier to read.

To calculate what age each client, you need is:

  • In Power BI Desktop, Click on Transform Data
  • Within the Power Query Editor window; start by choosing the column titled Birthdate.
  • Click on the add Column Tab to go under the "From Date & Time" section. Also, under Date select the date range.

This is all there is to it. This will calculate an amount that is the total of the column for birthdate, Birthdate column as well as the present date, as well as the time.

The age, however, when it appears in it's Age column, and does not really appear like an age. This is because it's not a length.

Duration

Duration is a particular kind of data format used with Power Query which represents the differences between two DateTime values. Duration is a combination with four values:

days.hours.minutes.seconds

These are the values you'll discover in these values. From a personal view, they shouldn't need to look for details like the ones listed above. There are methods to make sense of all the time. With the Duration menu choice, you'll notice the amount of seconds, minutes, hours, days and years.

For calculating the age in years like, say, you just need to go through Total Years.

The duration is measured in days , and then divided by 365, giving you the yearly value.

Rounding

Also, nobody claims that they're 53.813698630136983! They state it as 53, and then round it down. You can choose the Rounding option and round down from the Transform tab.

This will tell you what your age is in terms of years

It is also possible to clean other columns, in case you'd like (or you could have used transformations from the Transform tab to avoid creating new columns) The column could be named column as Age.

Things to Know

  • Refresh The age calculated using the method gets updated every time you are refreshing your data. Each time, it will compare your birthdate with the date and date at the time of refresh. This method is a pre-calculation of an age. If you, however, want the age calculation to be performed dynamically, employing DAX, here's how I have explained a procedure you could use.
  • Benefits of using Power Query: Benefits that come with age calculations made using Power Query is that the calculation is carried out at the time of refreshing your report. This is done by making use of a tool to make the calculations much easier and there's no need to add the expense of performing it using DAX as a measurement of runtime.
  • Another scenario This is not utilized to calculate the age based on birth date. This can be used to calculate inventory of goods as well as to determine the difference between two dates, and times, 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 in Computer engineering. The engineer has over 20 years' working in the fields of data analysis database, BI, programmingand development with a focus in Microsoft technologies. He has been an official Microsoft Data Platform MVP for nine consecutive years (from 2011 to present) due to his dedication for Microsoft BI. Reza has a long-running career as a writer and co-founder of RADACAD. Reza is also the co-founder and coordinator of Difinity Conference 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 few books on MS SQL BI and also is working on other books. He also was a frequent participant in online forums for technical issues such as MSDN and Experts-Exchange . He also serves as moderator of MSDN SQL Server forums, and holds an MCP and MCSE as well as an the MCITP for BI. He is the director of the New Zealand Business Intelligence users group. Also, he's the author of the highly popular workbook Power BI from Rookie to Rock Star, which is free to download and includes more that 17000 pages of data and an additional book called Power BI Pro Architecture published by Apress.
He is an International Speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, and SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's love is helping you discover the most effective methods to manage data, and is a Data enthusiast.This post was originally published by Power BI, Power BI from Rookie to Rockstar, Power Query and connected to Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great source to bookmark.

Post navigation

- Share different visual pages through Different Security Groups of Power BIAge's Year Calculation that works for Leap Year in Power BI using Power Query

Comments

Post a Comment

Popular posts from this blog

convert-to-jpeg

convert to bmp How to Create JPG to BMP for Free JPG format files can be very popular among people since all smartphones download images in JPG format but BMP format is not as common among the users as it is utilized in Windows computers. JPG as well as BMP as formats are excellent to store the files on computer computer but there is a difference between them. The majority of the time, JPG files have a small sizes but with low quality, while if you opt for BMP format to store your images , the quality of stored images will be better. If you're also struggling with problems with quality with your images, you can convert JPG to BMP using the aid of computer programs and online tools. In this article, we will provide five suggested JPEG conversion tools to Bitmap converters effortlessly in this article. If you're searching for a conversion from JPG BMP conversion online online UniConverter (Originally Media.io) ought to be your wish list NO.1. You'll need to ha...
Read more

Parts Per Million (ppm) Converter

Converter Parts Per Million (ppm) Parts Per Million (ppm) Converter for Gases This converter calculates the estimated amount (in the units [ppm[ppm]) Then it converts that number to those units of (mg/m 3. [mg/m 3] ) and reverses it. The unit ppm is employed in a variety of branches, in different ways. The use of ppm needs to be defined in the input fields below to ensure that it's converted into the appropriate unit. For more information about the use of ppm, go through the explanation below.In the input field for your Molecular Weight field, you can select from the drop-down menu or input your personal numbers to determine the molecular mass of gas. If the value of the molecular weight is not clear for you then take a look at our Molecular Weight Calculator.When you enter either or both or the the calculation in unit [ppm] within either the unit [mg/m 3 or [mg/m3The amount will be converted instantly and shown in the bottom section. The significance of th...
Read more

random number generator

Random Number Generator Use the generator to get an absolute randomly digitally secure number. It generates random numbers that can be utilized when accuracy of the results is essential for instance, when shuffling deck of cards to play the game of Poker as well as drawing numbers to win sweepstakes, giveaways or lottery. What is the most efficient way to select the best random number between two numbers? It is possible to use this random number generator for you to create a genuine random number from any two numbers. For instance, to get an random number that is in the range of one to 10 (including 10, you need to enter 1 first in the input field and 10 in the second field, then click "Get Random Number". The randomizer will select one from the numbers 1 through 10 randomly. To generate an random number between 1 and 100, follow the same procedure with 100, but it falls within the second field of the randomizer. To simulation of rolling dice, the number ...
Read more