Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, as DAX is the most popular language usedin several functionsin Power BI, many do not know the functions available in Power Query. In this article, I'll detail how easy it is to calculateAge within Power BI with Power BI. It is a great methodis very beneficial when the computation of an agecan be carried out on a pre-calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table, which is part of the AdventureWorksDW table and is one of the columns that is a time column. I've removed some of the extra columns in order to make the table simpler to read.

In order to calculate the age of each client, you have to do is:

  • In Power BI Desktop, Click on Transform Data
  • On 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.

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

However, the appearance of the age within an Age column, and does not appear to be an actual age. It is due to the fact that it is an expression of time.

Duration

Duration is a special kind of data format used on Power Query which represents the difference in two DateTime values. Duration is composed with four values:

days.hours.minutes.seconds

You will discover in these values. From a personal viewpoint, they shouldn't have to search for specifics like those above. There are methods that could get every part of the time. Utilizing the Duration menu option, you'll observe the quantity of seconds, minutes, hours, days, and years.

For calculating the age in years like, for instance it is as simple as going on to Total Years.

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

Rounding

And lastly, no one says that they're 53.813698630136983! they claim 53 with a rounding down. You can select the Rounding option and round down from the Transform tab.

This will give you an indication of how old you are:

It's then possible to purify other columns should you wish (or you could have made use of transformations within the Transform tab to avoid creating new columns.) The column could be named column: Age.

Things to Know

  • Refresh: The age calculated using this method is updated every time you are refreshing your database. Every time, it compares your birthdate to the date and time at the time of refresh. This method is a pre-calculation of an age. If, however, you require the calculation of age to be done dynamically by using DAX, here's how I have explained a procedure you can use.
  • Why you should choose Power Query: Benefits of performing age calculations in Power Query is that the calculation is made at the time of refreshing your report. This is accomplished making use of a tool to make the calculation much simpler, and there's no need for the extra expense of performing it using DAX as a measurement of time.
  • Another scenario This is not used to calculate the date of birth as the age. This can be used to calculate inventory of goods as also for the differences 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 has a BSc of Computer engineering. Over 20 years' experience in the field of data analysis databases programming, BI, and development that is primarily focused specifically on Microsoft technologies. He is a Microsoft Data Platform MVP for nine years in a row (from 2011 to present) due to his devotion towards Microsoft BI. Reza is known as an prolific author and co-founder of RADACAD. Reza is also co-founder and 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 writing some others, He is also a regular participant in online forums on technical matters like MSDN and Experts-Exchange , as well as moderator for MSDN SQL Server forums, as well as an MCP and an MCSE and the MCITP for BI. He is the director of the New Zealand Business Intelligence users group. Additionally, he is the writer of the incredibly popular workbook Power BI from Rookie to Rock Star, which is free to download and includes more than 17000 pages of content and an additional book titled Power BI Pro Architecture published by Apress.
This speaker has been an International Speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday in addition to SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help you discover the most effective solutions for data, and he's a Data enthusiast.This blog post appeared in 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 article is a good guide to bookmark.

Post navigation

- Share various visual pages using different security groups within 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

age calculator

Image
How to create an age calculator in Excel? Excel Now that you know how to make an age formula in Excel, you can build a custom age calculator, for example this one:https://onedrive.live.com/embed?c What you see below is an embed Excel Online sheet, so do not hesitate to input your birthdate within the appropriate cell and you'll find your age in a moment. Calculators use these formulas to calculate age in relation to the age of the date of birth in cell A3 as well as the current date. Formula in B5 calculates age in years, months, and days: =DATEDIF(B2,TODAY(),"Y") & " Years, " & DATEDIF(B2,TODAY(),"YM") & " Months, " & DATEDIF(B2,TODAY(),"MD") & " Days" Formula in B6 calculates age in months: =DATEDIF($B$3,TODAY(),"m") Formula in B7 calculates age in days: =DATEDIF($B$3,TODAY(),"d") If you have some experience working with Excel Form controls...
Read more

BMI Calculator

BMI Calculator It is this calculator that is Body Mass Index Calculator ( BMI calculator ) is an important tool that will help you assess the body's weight. With the help of the calculator, you can determine if you're an ideal weight. This can reduce the chance of developing obesity-related illnesses, including high blood pressure as well as the heart health issues of type 2 diabetes and many other. What Is BMI (Body Mass Index)? BMI is an indicator instrument that can tell you whether it is overweight, overweight normal-weight or underweight at your weight. BMI evaluates your body mass to determine if your body mass falls to the healthy weight range. In addition to measurement of your height, BMI also depends on the age of your body and gender. You can make use of the internet-based BMI calculator to help you understand the significance of. BMI is a measurement of an individual's leanness or corpulence based on their weight and height, and is designed to ga...
Read more

convert the image to png

convert to BMP Do you wish to convert an Images of one type to another? Like PNG to JPG or vice versa? Or maybe even change an image to PDF? It's either for performance, quality, or any other reason there are many situations where you're required alter the format of an image. In this article, we'll share six great conversion image tools that let you swiftly modify any image for one file format another format (or even bulk convert images if needed). The best six tools to convert image into a different format In the wake of our discussion on why it might be beneficial to convert images to an image form, lets look at the top six options. They're all pretty similar capabilities, so the one you choose that is the most appropriate for you will depend on the amount of images that you want to convert regularly. Convertio CloudConvert PhotoBulk Tinybeest Online Convert Convertio ...
Read more