Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, because DAX is by far the most well-known language usedin several functionsin Power BI, many people do not fully appreciate the capabilities of Power Query. In this article, I'll discuss how simple it is to calculateAge in Power BI using Power. This methodis extremely helpful in situations where the calculation of the agecan be done on a pre-calculated row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table, which is one of AdventureWorksDW table with an older column. I've eliminated a few extra columns, to make it easier to read.

In order to calculate what age every person who purchases from you, all that you need is:

  • In Power BI Desktop, Click on Transform Data
  • The Power Query Editor window; begin by selecting the Birthdate column.
  • Click on the Add Column Tab found under the "From Date & Time" section. And under Date choose the appropriate age range.

That's about it. This can calculate an amount which is the sum of the Birthdate column as well as the current date as well as the time.

But, the age appears within that Age column, does not seem to look like an actual age. It's because it's not a length.

Duration

Duration is a unique type of data format that is found with Power Query which represents the difference between two DateTime values. Duration is a mixture of four numbers:

days.hours.minutes.seconds

That's what you'll see in the above values. In reality, from an individual's view, they shouldn't need to seek out specifics such as those above. There are methods to be used to determine every minute of the time. With the Duration menu choice, you'll see the number of seconds to minutes, hours and years out of it.

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

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

Rounding

Finally, no one claims the number 53.813698630136983! They say 53, with a rounding down. You can easily choose the option to round and round down the Transform tab.

This will reveal what your age is in terms of years

It's also possible to clean other columns, if desired (or you could have applied transformations from the Transform tab to avoid making new columns.) It is possible to name this column Age.

Things to Know

  • Refresh: The age calculated using this method can be refreshed each time you refresh your data. Every time, it will compare your birthdate with the date and timing at the time of refresh. This method is an algorithm for pre-calculating your age. If, however, you want the age calculation to be carried out dynamically, using DAX here is how I have explained a procedure is possible to use.
  • Why you should choose Power Query: Benefits of doing age calculations using Power Query is that the calculation is carried out during the refresh of 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 cost of calculating it with DAX as a measurement of the runtime.
  • Another scenario is that this isn't used to calculate the birthdate as an age. It could be used to determine the inventory of items as or the difference in two 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 holds a BSc in Computer engineering. More than twenty years' experience in the field of data analysis databases programming, BI, development and programming with a focus around Microsoft technologies. He has been a Microsoft Data Platform MVP for nine consecutive years (from 2011 to the present) because of his commitment for Microsoft BI. Reza can be described as a prolific author and co-founder of RADACAD. Reza is also the co-founder and 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 wrote several books on MS SQL BI and also is working on a number of other books. He was also a regular participant in online forums on technical issues such as MSDN and Experts Exchange, as well as moderator for MSDN SQL Server forums, and is an MCP and the MCSE, as well as MCITP for BI. The director is of the New Zealand Business Intelligence users group. Also, he's the author of the incredibly popular workbook Power BI from Rookie to Rock Star, which is accessible for free and contains more that 17000 pages of data and an additional book called Power BI Pro Architecture published by Apress.
The speaker is 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 dream is to help you discover the most effective solutions to deal with data. And he's an avid Data enthusiast.This post was originally published by Power BI, Power BI from Rookie to Rockstar, Power Query and related to Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great resource for you to bookmark.

Post navigation

- Share different Visual Pages using different security groups in Power BIAge's Year Calculation that works for Leap Year in Power BI with 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 gaseous substances The converter calculates how much volume of gas is measured in ppm units and converts it into the measurements [mg/m 3[mg/m 3] and in reverse. The unit ppm is used across a range of branches, in various ways. The usage of ppm must be noted in the fields that are below for input, so that you can determine the way it will be converted into the appropriate unit. For more information on the usage of ppm, take a look at the instructions below.In this section, you will find the entry field for The Molecular Weight you could either choose from the drop-down menus or add the molecular mass of the substance. If you are unsure of the molecular value for you, then check out our Molecular Weight Calculator.When you enter this formula, you can enter or the formula or the formula in units of [ppm] or in units of [mg/m]. 3. The number is then converted directly and displayed in the lowe...
Read more

power-converter

Converter Parts Per Million (ppm) Parts Per million (ppm) Converter for GasesThis converter calculates how much of the estimated amount (in increments in units of [ppmin] of) to convert it to an equivalent unit of measurement [mg/m 3. [mg/m 3] ] and vice versa and reverse. The unit ppm is used in a variety of branches in a variety of ways. The purpose of ppm's use is to be noted in the fields for input below, and in the manner it is converted into the proper unit. For more details on the meaning of ppm check out the definition below.In the fields for input for the Molecular Weight field, you can select the drop-down box or enter your own number into the field to indicate the molecular weight of the gas. If the molecular value is unfamiliar to you, you should use our Molecular Weight Calculator.When you input or both and you enter the formula in units of [ppm] and with units like [mg/m 3 or [mg/m3] the amount will be converted immediately and displayed...
Read more

LENGTH CONVERTER

  LENGTH CONVERTER Select the length you want to use, and then type in the amount that you'd like to convert to a different measurement of length within the "From" field, and choose the measurement you'd like to convert it to in the "To" field.   The conversion results will be displayed in the "To" field right after you click on any unit. "Length" is known as the term"length," which you've likely heard of , is described in the context of "length" as a precise  measurement  of everything from one end to the other.   It's not easy to find precise measurements in converting miles to kilometers.   Sometimes, we need an estimate of the time it takes for others to travel there, or of the time it takes to get there and that's the reason length converters can prove useful in calculations.   Measurement  Converter is a tool that can be used in accordance with the conditions because it can convert any length type, it...
Read more