Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, since DAX is one of the used language usedin several functionsin Power BI, the majority of users don't fully appreciate the capabilities of Power Query. In this article, I'll explain how easy it is to calculateAge in Power BI with Power BI. This methodis extremely beneficial for situations when the computation of an agecan be performed on pre-calculated row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table that is part of the AdventureWorksDW table which includes the age column. I've removed a few additional columns, to make it simpler to navigate.

To calculate the age of every client, you need is to:

  • In Power BI Desktop, Click on Transform Data
  • Within the Power Query Editor window; start by clicking on the Birthdate column.
  • Click on the add Column Tab to go under the "From Date & Time" section. Under Date choose the appropriate age range.

It's that simple. it. This will calculate an amount which is the sum of the column for birthdate, Birthdate column and the present date, as well as the time.

However, the appearance of the age within the Age column, does not seem to look like an age. It is due to the fact that it is an actual duration.

Duration

Duration is a particular kind of data format used with Power Query which represents the difference between two DateTime values. Duration is a mix comprising four figures:

days.hours.minutes.seconds

This is what you will observe in the following values. From a personal perspective they shouldn't be expected to find specifics similar to the ones in the above. There are methods that could find every moment of time. When you select the menu option Duration, you'll observe the quantity of seconds, minutes, hours, days, and years out of it.

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

The duration is calculated in days . It is then subdivided by 365 to calculate the annual value.

Rounding

And lastly, no one says the number 53.813698630136983! they say it as 53 with a rounding down. It is easy to select the Rounding option and round down on the Transform tab.

This will reveal that you're old enough to be

It's also possible to remove other columns if desired (or you could have utilized transformations in the Transform tab to stop creating new columns.) You could name this column as Age.

Things to Know

  • Refresh The age calculated by this method will be updated every time you are refreshing your database. Every time, it will compare your birthdate with the date and moment of the refresh. This method is a pre-calculation of an age. If you require the calculation of age to be dynamically performed, using DAX here is how I described a method you could make use of.
  • Why you should choose Power Query: Benefits of calculating age in Power Query is that the calculation takes place during the refresh of your report. This is performed by using a program that allows for easier calculation, and eliminates an additional cost to calculate the calculation using DAX as a measure of runtime.
  • Another possibility is that it cannot be used to calculate the date of birth as the age. It could be used for inventory of products as and 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 has a BSc of Computer engineering. He has more than 20 years of experience in data analysis , database programming, BI, development and programming mostly focused upon Microsoft technologies. He is a Microsoft Data Platform MVP for nine years (from 2011 until now) due to his dedication for Microsoft BI. Reza has a successful writer and co-founder of RADACAD. Reza is also co-founder as well as co-organizer for the 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 writing other books. He is also a regular participant in online forums for technical matters like MSDN and Experts-Exchange . He also serves as moderator for MSDN SQL Server forums, and holds an MCP and MCSE as well as an the MCITP for BI. As the group's director, he is responsible for the New Zealand Business Intelligence users group. He is also the author of the incredibly popular workbook Power BI from Rookie to Rock Star, which is freely available and contains more that 17000 pages of data and an additional book , Power BI Pro Architecture published by Apress.
Speakers are an International Speaker in 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 passion is to help you discover the most effective ways to use data, and the fact that he's an avid Data enthusiast.This article was published in Power BI, Power BI from Rookie to Rockstar, Power Query and connected to Power BI, Power BI from Rookie to Rock Star, Power Query. This is a great resource to save to your bookmarks.

Post navigation

- Share various visual pages by using different security groups of Power BIAge's Year Calculation that works for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

Parts Per Million (ppm) Converter

Converter Parts Per Million (ppm) Parts per Million (ppm) Converter for gases The converter calculates the determined amount (in units ) of [ppm(in unit of [ppm) in order to convert it into one of the unit (mg/m 3. [mg/m 3] and vice versa. The unit ppm is used throughout the various branches, in various ways. The use of ppm has been specified in the fields to input below, so that it could be converted to the correct unit. For more information on the use of ppm, go through this explanation below.In the fields for input for Molecular Weight, you can select the drop-down option or type in your own number in the field to indicate the molecular weight for the gas. If the molecular mass isn't known for you, try our Molecular Weight Calculator.When you enter either the formula or n the formula in units of [ppm] or the units [mg/m 3 or [mg/m 3The value will be instantly converted and will be displayed in the lower part of the screen. The significance is automatically determine...
Read more

power-converter

Converter Parts Per Million (ppm) Parts per million (ppm) Converter for Gases This converter calculates an estimated quantity (in units that is [ppm]) and converts it into those units (mg/m 3. [mg/m 3] ) and reverse. The unit ppm has been used in many branches, in a variety of ways. The application of ppm has been clearly defined in the input fields below, as well as the method by which it is able to be converted into the appropriate unit. For more details on the use of ppm, take a look at the clarification below.In the relevant field you can enter into the Molecular weight field. you can select the drop-down menu or type in the value of your choice that corresponds to the molecular mass of gas. If the concept of molecular weight doesn't seem clear to you, then we suggest you try our Molecular Weight Calculator.When you enter either or or the calculation in unit [ppm] in the values (mg/m 3 or [mg/m 3] The quantity will be converted immediately and is displayed in the...
Read more

random number generator

TEMPLATE DESCRIPTION It's amusing to know how much time have you spent on earth. Also, do you know how many minutes or days have passed since you got married? Just check with your Family Age Calculator in Excel to find out! This Excel template can be used as a basic calculator for your family members. It is constructed by two sections: Dashboard Age Counter Dashboard It comes with appealing design and a simple dashboard. It will allow you to input the name of your family members, their birthday and it can also be used to show how long have passed since the parents wedding. Age Counter: Here the template will calculate different things regarding the age you input. How many days, months, Hours, Minutes and Minutes did resided! The calculations will be based on to the current date in your system using TODAY() function in Microsoft Excel. Print your own copy of this Excel spreadsheet template, and export it to PDF as like. Are you a famil...
Read more