Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method for calculating the age. But, because DAX is the primary language usedin numerous computationsin Power BI, many are not aware of this function in Power Query. In this article I'll explain how easy it is to calculateAge in Power BI using Power BI. This methodis extremely beneficial in situations where your estimation of the agecan be done as an earlier calculated row by row basis.

Calculate Age from a date

Below you can see the DimCustomer table that is part of the AdventureWorksDW table that acts as the birthdate column. I've removed a few of the extra columns to make it easier to read.

In order to calculate your age for each customer, all you need is to:

  • In Power BI Desktop, Click on Transform Data
  • In Power Query Editor window; pick the Birthdate column first.
  • go to add Column Tab, and then click on the "From Date & Time" section, and under Date, choose Age

That's it. this is how you calculate an amount that is the sum of the Birthdate column, along with the current date and time.

However, the age appears under the Age column, doesn't appear to be an actual age. It's because it's an actual duration.

Duration

Duration is a particular data type within Power Query which represents the difference of two DateTime values. duration is a combination of four values:

days.hours.minutes.seconds

and that is how you can interpret the data above. However, for the viewpoint of the user it is not expected of them to go and read the particulars of this. There are methods that could get every part in the period. By selecting the Duration menu, you will see that you are able to extract the number of seconds and minutes, hours, days and years out of it.

To help in calculating the age in years such as, for instance, you can simply hit Total Year:

Note that the duration is calculated in days . It is after that divided by the number of days, to yield the yearly amount.

Rounding

It's the truth, no one declares they are 53.813698630136983! They say 53, which is rounded down. It is easy to select Rounding and round down from the Transform tab.

This will show you your age in years:

You can then clean the other columns, if desired (or perhaps you've made use of transformations using the Transform tab to avoid having to create new columns), and call this column; Age:

Things to Know

  • Refresh The age that is calculated this way will get updated every time you are refreshing your data. and each time will compare the birthdate to the date and time at the time of refresh. This method is a pre-calculation of an age. If, however, you require that the calculation be made dynamically using DAX here is how I described the method you could employ.
  • The reason behind Power Query: Benefits of performing an age calculation using Power Query is that the calculation is done during the refresh of your report, using an instrument that makes the calculation much easier and faster, and there won't be extra overhead in calculating it using DAX to measure runtime.
  • Alternative scenarios It is not used to calculate the age from birthdate. this can be used for stock-level age for inventory items and the differences between two dates and times from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds the BSc on Computer engineering; he is more than twenty years' experience in the field of data analysis and BI, databases, programming, and development mostly in Microsoft technologies. He is a Microsoft Data Platform MVP for nine consecutive years (from 2011, until now) for his passion for Microsoft BI. Reza is a prolific blog writer, and the co-founder and editor of RADACAD. Reza is also the co-founder and co-organizer for the Difinity Conference located 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 wrote some books on MS SQL BI and also is writing more books. He was also a regular participant in online forums for technical issues such as MSDN and Experts-Exchange and was also moderator of MSDN SQL Server forums, and is an MCP and MCSE as well as an MCITP of Business Intelligence. He is the leader in the New Zealand Business Intelligence users group. He is also the creator of the highly acclaimed book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and The Power BI Pro Architecture published by Apress.
The speaker is an International speaker at 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 passion is to help users find the best data solution. He is a Data enthusiast.This article was published by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is filed under Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

- Share Different Visual Pages with Different Security Groups in Power BIAge in Years Calculation that works for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

Online Age Calculator

The evolution of the scientific calculator The scientific calculator Although we may have begun using fingers and toes to count, mankind has been creating machines that aid in calculations for many centuries. From the earliest counting devices to the modern calculators many things have been created in the space between. Nowadays, professionals and students alike have access to technological calculators capable of performing complex mathematical functions as well as solving complex equations. They can save a lot of time when compared to writing out individual questions and equations. How did the inventors make the leap from the earliest abacus hundreds of years ago to the current scientific calculator? What is a scientific calculator? Scientific calculators , sometimes referred to as digital calculators , are electronic devices that are able to solve a variety of calculations that are used in math, engineering and science. Basic calculators generally h...
Read more

partsPer-converter

 <h1>  <a href="https://aboneapp.com/#/partsPer-converter">Converter Parts Per Million </a>(ppm) </h1> <figure>  <table><tbody><tr><td>      Parts per Million (ppm) Converter for Gases This <a href="https://aboneapp.com/#/pace-converter">converter</a>calculates the value of the volume of gas that is measured as [ppm] to convert it into equivalent units such as (mg/m <sup>3</sup>and vice versa. reverse. The unit ppm may be utilized across a range of branches in different ways. The usage of ppm must be specified in the fields that are input below, according to the way it is to convert to the correct unit. For more information on the meaning of ppm, take a look at the explanation below.In the input fields of Molecular Weight , you can select the drop-down list or input the molecular value in the gas. If the molecular weight of the gas is unclear to you, you can try our Molecular Weig...
Read more

Random Number Generator

Random Number Generator Utilize this generator to obtain an absolutely random and cryptographically safe number. It generates random numbers that can be used where unbiased results are critical, such as playing shuffled decks of cards in a game of poker or drawing numbers for giveaways, lottery or sweepstake. How to pick what is a random number from two numbers? You can utilize this random number generator to generate an authentic random number among any two numbers. For instance, to generate an random number in the range of one to 10 (including 10, input 1 to the top box and 10 in the secondfield, after which press "Get Random Number". Our randomizer will pick a number from 1 through 10, all at random. For generating the random number between 1 and 100, repeat the process as above, except that you put 100 for the other field within the randomizer. To simulate a dice roll the range should be from 1 to 6 for a standard six-sided dice. If you want to gen...
Read more