Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, because DAX is the most popular language usedin many computationsin Power BI, many are unaware of this feature that is available in Power Query. In this blog , I'm going to explain how simple to calculateAge in Power BI using PowerBI. It is a methodis extremely effective in situations where your estimation of the agecan be calculated on a pre-calculated the row basis.

Calculate Age from a date

Below, you will see the DimCustomer table that can be found in the AdventureWorksDW table that has a birthdate column. I've removed the columns that aren't needed so that it is easier to read;

In order to calculate the actual the age for each purchaser the following information is required:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; start by selecting the column for Birthdate.
  • go to the Add Column Tab, and then select"Add Column Tab," then click on the "From Date & Time" section. And under Date select the date range.

That's all there is. This is how you calculate an amount that is the sum of the Birthdate column, along with the time and date column.

But, the age appears as a number in the Age column, it doesn't actually appear to be an age. It's due to an actual Duration.

Duration

Duration is a distinctive type of data format used in Power Query which represents the differences in the two DateTime values. Duration is a combination of four values:

days.hours.minutes.seconds

That's the way you read the numbers above. But from the standpoint of the user it is not their responsibility to read particulars like that. there are ways that you can get every portion of the time. When you select the Duration menu you'll notice that you can extract the amount of seconds as well as minutes, hours months and days from it.

To assist in calculating the age in years by way of example it is easy to select Total Years:

The duration was calculated in days . Then, it was divided by 365 to provide you with the annual value.

Rounding

In the end, no one declares your age as 53.813698630136983! The number is 53. This is rounded down. You can select Rounding as well as Round Down by clicking on the Transform tab.

This will show you how old you are:

You can clean other columns, if you want (or there could be that you made use of transforms from the Transform tab, to prevent creating new columns.) It is possible to name this column"Age:

Things to Know

  • Refresh The information's age calculated this way will be refreshed each time you refresh your database. Each time, the system will be able to compare the birthdate to the date and the date and time of refreshing. The method will be an algorithm to predict the age. If you'd like to have the calculation to be executed dynamically by using DAX this is the method I explained how to make use of.
  • The motivation behind Power Query: Benefits from using age calculation in Power Query is that the calculation takes place at the time of refreshing your report. This is done by using a tool that makes calculation easy and quicker, and there's no additional cost in calculating it using DAX for a measurement of of runtime.
  • Other scenarios intended for the calculation of age from the birthdate. It can be used to calculate the age of inventory of items and also the differing dates and dates of each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc with a major of Computer engineering. Over 20 years' work experience in the field of data analysis and BI, databases, designing, and programming primarily with Microsoft technologies. He has been an Microsoft Data Platform MVP for nine continuous years (from 2011, until now) because of his love for Microsoft BI. Reza is an incredibly prolific writer and co-founder at RADACAD. Reza is also co-founder and organizer of the Difinity event 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 also wrote a few books about MS SQL BI and also is writing some other books. He was also a regular participant on technical forums on the internet such as MSDN as well as Experts-Exchange and was a moderator for MSDN SQL Server forum and is an MCP, MCSE and with an MCITP in BI. He is also the leader for the New Zealand Business Intelligence users group. This group is also the writer of the book highly praised Power BI from Rookie to Rock Star, which is absolutely free and contains over 1700 pages of information along with a separate book called Power BI Pro Architecture published by Apress.
This is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL group for users. And He is a Microsoft Certified Trainer.
Reza's ambition is to help people find the best solutions for data. is a Data enthusiast.This post was filed with Power BI, Power BI from Rookie to Rockstar, Power Query and is categorized under Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic guide to bookmark.

Post navigation

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

Comments

Post a Comment

Popular posts from this blog

What is Calorie Counting?

What Is A Calorie? When we speak of eating or exercising, we tend to discuss in terms of calories . Menus at restaurants will indicate the number of calories in each meal. Treadmills indicate the amount of calories that are burned. Dieters monitor calories every day while others giddyly proclaim that calories don't count on weekends. What exactly is an calorie in the first place? "The calorie is nothing more than a measurement, just like inches or kilowatts," says Lauri Wright the assistant professor of nutrition and dietetics of the University of North Florida and spokesperson for the Academy of Nutrition and Dietetics. "It just represents how much energy is contained in the food or how much energy is used by the body." What's in a Calorie When we talk about calories in our bodies as well as in our food , what we are talking about is actually kilogramcalories, Wright claims. A calorie, often called a small-sized calorie is the quanti...
Read more

Random Number

Random Number Generator If you're trying to generate random numbers , you've come to the right place. Our Random Number Generator gives you various options when you're looking to generate random numbers. The entire process is quite easy. The first step is to select the number of random numbers you need to be generated. Then, select the number range you'd like the random number to be generated within. Once done, click"Generate Random Numbers " Generate Random Numbers " button and they will appear instantly. You can select a certain number range or can use one of the many set-number generators. These comprise random numbers between 1 and 10, random numbers between 1 and 100, and random numbers between 1 and 1000. If you need create negative numbers we have the option of random numbers from -100 to 100. There are a variety of reasons to employ a random number generator . It is used in a broad range of fields including statistical sampling as we...
Read more

Above Meaning In Tamil

  Above Meaning In Tamil - தமிழ் பொருள் விளக்கம் " Above " தமிழ் மொழிபெயர்ப்பு, பொருள், வரையறை, விளக்கம் மற்றும் தொடர்புடைய சொற்கள் மற்றும் புகைப்பட எடுத்துக்காட்டுகள் - நீங்கள் இங்கே படிக்கலாம். Above   ♪ : / əˈbəv / சொற்றொடர் : - உச்சியில் மேலே மேலே பெயரடை : adjective சொற்றொடர் : conounj பெயர்ச்சொல் : noun கடந்த காலம் முன்னிடைச்சொல் : preposition மேலே ஒன்றிற்கும் மேலாக மேற்சொன்ன மேலேகண்ட உயரமாக தலைக்குமேல் விண்ணில் முன்னிடத்தில் மேலே மேம்பட்டு அப்பாற்பட்டு அந்த மலைக்கு மேல் கோவில் உள்ளது வீட்டின் மேல் நீர்தொட்டி அமைக்கப்பட்டிருந்தது உயரத்தில் மேலே இருக்கிற மேல் அப்பால் அவ்வருகு உபரி ஊர்த்துவம் விளக்கம் : Explanation நீட்டிக்கப்பட்ட இடத்தில் மற்றும் தொடாத நிலையில். மேல்நோக்கி நீட்டித்தல். ஒரு பக்கத்தை விட உயர்ந்தது; கவனிக்கவில்லை. விட உயர்ந்த மட்டத்தில் அல்லது அடுக்கில். தரத்தில் அல்லது தரத்தை விட உயர்ந்தது. விட உயர்ந்த அந்தஸ்து அல்லது மதிப்புடையதாகக் கருதப்படுகிறது; மிகவும் நல்லது. முன்னுரிமை. விட அதிக அளவு அல்லது ச...
Read more