Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, since DAX is the most used language usedin several functionsin Power BI, many do not realize the function available in Power Query. In this article I will detail how easy it is to calculateAge within Power BI and Power BI. The methodis extremely helpful in cases where the computation of an agecan be calculated on a row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table that is an element of the AdventureWorksDW table with one of the columns that is a time column. I've removed some of the extra columns, to make it easier to navigate.

In order to calculate an age range for every person who purchases from you, all that you have to do is:

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

That's all there is to it. This can calculate an amount that's the product of the Birthdate column as well as the current date and the time.

However, the age appearing within that Age column, but it doesn't appear to be an actual age. It's because it's a duration.

Duration

Duration is a distinct type of format for data within Power Query which represents the difference in two DateTime values. Duration is a mixture from four different numbers.

days.hours.minutes.seconds

These are the values you'll observe in the following values. However, from a user's point of view, they shouldn't have to search for specifics like the ones mentioned above. There are ways to find every moment of time. By using the Duration menu, you'll notice the amount of seconds to minutes, hours, days, and years out of it.

To calculating the age in years like, for instance you just need to go into Total Years.

The duration is calculated by days and then divided by 365. This gives you an annual number.

Rounding

And lastly, no one says you are 53.813698630136983! They state it as 53 with a rounding down. You can choose the rounding option, and then round down on the Transform tab.

This will let you know what your age is in terms of years

It's also possible to remove other columns in case you'd like (or you could have used transformations under the Transform tab to stop creating new columns.) This column can be named. column: Age.

Things to Know

  • Refresh: The age calculated by the method gets updated each time you are refreshing your dataset. Each time, it compares your birthdate with the date and date at the time of refresh. This method is an initial calculation of age. If you need the age calculation to be performed dynamically, using DAX here is how I explained the method you can utilize.
  • Arguments for choosing Power Query: Benefits that come with age calculations made using Power Query is that the calculation takes place at the time of refreshing your report. This is accomplished making use of a tool to make the calculation easier, and there's no need to add the expense of performing it using DAX to measure running time.
  • Another scenario is that this isn't used to calculate the birthdate as an age. This can be used to calculate the inventory of items as well as 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 the BSc in Computer engineering. Over 20 years' experience in data analysis , database programming, BI, development and programming that is primarily focused specifically on Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years (from 2011 to present) due to his dedication to Microsoft BI. Reza is a prolific author and co-founder of RADACAD. Reza is also co-founder as well as organizer 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 wrote several books on MS SQL BI and also is working on other books. In addition, he was a frequent participant in online forums on technical matters like MSDN and Experts-Exchange . He also serves as moderator of MSDN SQL Server forums, and is an MCP , MCSE as well as an MCITP for BI. He is the director of the New Zealand Business Intelligence users group. In addition, he's the creator of the very well-known books such as Power BI from Rookie to Rock Star, which is free to download and includes more that 17000 pages of content and an additional book called Power BI Pro Architecture published by Apress.
Speakers are an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday along with SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's passion is to help you find the most efficient solutions for data, and is a Data enthusiast.This blog post appeared in Power BI, Power BI from Rookie to Rockstar, Power Query and associated with Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic source to bookmark.

Post navigation

- Share Different Visual Pages by using 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

power-converter

Converter Parts Per Million (ppm) Parts per Million (ppm) Converter for GasesThis converter calculates an calculated quantity (in the increments of units [ppmin] of [ppm) to convert it into the equivalent measurement unit [mg/m 3. [mg/m 3] ] or vice versa and in reverse. The unit ppm can be used in different branches in a variety of ways. The usage of ppm needs to be described in the input fields below and the way it can be converted into the proper unit. For more information on the usage of ppm, check out the definition below.In the input fields on the molecular weight field, you can choose the drop-down option or enter your own number in the field for molecular mass of gas. If the molecular weight of the gas is unknowable to you, then we recommend using our molecular weight Calculator.When you input either or both the calculations in units of ppm or with units like (mg/m3) 3 or [mg/m3The quantity will be converted immediately and will be shown in the fi...
Read more

LENGTH CONVERTER

  LENGTH CONVERTER Choose the length you want to use, and then type in the amount that you'd like to change into a different length measurement within the "From" field, and select the unit you'd prefer to convert it into within the "To" field.   The result of the conversion will be visible within the "To" field right after you click on any unit. "length" or the term"length," which you've probably heard of, is described by the term "length" as a precise  measurement  of anything from one end to the other.   It's difficult to locate precise measurements for converting miles into kilometers.   Sometimes, we require an estimate of the time it will take others to travel to the destination or the amount of time it takes to reach there, and that's why length conversion tools can prove useful in calculations.   Measurement  Converter can be used in accordance with the conditions as it can convert any length type. I...
Read more

What is the complete form of CRPF?

  What is the full  form of CRPF ? About CRPF The  Central Reserve Police Force  was created as Crown Representative's Police on 27th July 1939.   It was transformed into the Central Reserve Police Force on the promulgation of the CRPF Act on 28th December 1949.   It has been in existence for more than 82 glorious years.   The Force has grown into a big organization with 246 Bns (including 203 executive Bns,05 VIP Security Bns and 6 Mahila Bns, 15 RAF Bns, 10 CoBRA Bns, 05 Signal Bns and 01 Special Duty Group, 01 Parliament Duty Group Special Duty Group, 01 Parliament Duty Group), 43 Group Centres, 22 Training Institutions, 03 CWS, 07 AWS 03 MWS/SWS Hospitals with 100 beds 18 composite hospitals of 50 beds and 06 Field hospitals. Mission The role of Central Reserve Police Force Central Reserve Police Force shall help the government to maintain Rule of Law, Public Order and Internal Security efficiently and effectively as well as to ensure the protectio...
Read more