Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method for calculating the age. However, as DAX is the primary language usedin numerous computationsin Power BI, many aren't aware of this feature in Power Query. In this article, I'll demonstrate how simple it is calculateAge in Power BI using Power BI. The methodis extremely useful in cases where your estimation of the agecan be calculated on an earlier calculated row by row basis.

Calculate Age from a date

Below you will find the DimCustomer table which is comprised of AdventureWorksDW table, which is an entry point for the birthday column. I've taken out a few of the extra columns making it simpler to understand.

In order to calculate your age for each customer the only thing you need to do is:

  • In Power BI Desktop, Click on Transform Data
  • In Power Query Editor window, choose the first column for the Birthdate column.
  • Click on the Add Column Tab after which choose"Add Column Tab" and then on "From Date & Time" section, and under Date, choose the age range.

That's that. this is the method you calculate an amount which is the total of the column for Birthdate column, together with the current date and time.

But, the age that appears under the Age column, and doesn't seem to be the actual age. It's because it's not an actual length.

Duration

Duration is a particular form of data that is part of Power Query which represents the variation between the two DateTime values. duration is a combination of four numbers:

days.hours.minutes.seconds

This is how you determine the meaning of the data. In the eyes of the user it is not the responsibility of them to study the full details of this. There are methods to be able to get every component of the time. When you select the Duration menu, you will find that it is possible to extract the number of minutes and seconds along with days, hours and years out of it.

To assist with calculating the age in years for example, you can simply hit the Total Year:

Note that the duration is calculated in days . It is after that divide by the total number of days, to calculate the annual amount.

Rounding

No person declares that they are 53.813698630136983! They say 53, which is an arbitrary number that is rounded down. It's easy to select Rounding and round down from the Transform tab.

This will tell you what your old age is in terms of years.

You can then clean the other columns, should you like (or maybe you've taken advantage of transformations by using the Transform tab to avoid having you create new columns), and call this column"Age:

Things to Know

  • Refresh The age that is calculated in this manner is changed each time you're refreshing your information. And each time it will be comparing the birth date to the date and time at the time of refresh. This method is pre-calculating an age. If, however, you require the calculation be done dynamically with DAX Here is how I described the method that you can use.
  • The rationale for Power Query: Benefits of doing an age calculation with Power Query is that the calculation is made in the course of refreshing your report, and using an instrument which makes the calculation simpler and faster, plus there's no additional cost when it is calculated using DAX to gauge runtime.
  • Alternative scenarios It can't be utilized to calculate the age from birthdate. It can be used to calculate inventory-level ages for inventory items as well as the differences between two dates or 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 on Computer engineering. He has more than 20 years old. experience in the area of data analysis, databases, BI and development, mostly using Microsoft technologies. He is an official Microsoft Data Platform MVP for 9 consecutive years (from 2011 until now) because of his enthusiasm to Microsoft BI. Reza is an avid blogger as well as the co-founder and editor of RADACAD. Reza is also the co-founder of and co-organizer of 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 has written several books on MS SQL BI and also is working on more books. He was also a regular participant in forums online for technical issues like MSDN and Experts-Exchange and was also moderator of the MSDN SQL Server forums, and is an MCP and MCSE and an MCITP of Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. Additionally, the group is creator of the highly acclaimed publication Power BI from Rookie to Rock Star, which is free and has more than 170 pages of content. 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 dream is to help users find the perfect data solution. He is an avid Data enthusiast.This blog post has been published by Reza in 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 entry was filed under Power BI. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

- Share Multiple Visual Pages using different security groups in Power BIAge Calculation in Years that is used to calculate Leap Year in Power BI using Power Query

Comments

Post a Comment

Popular posts from this blog

the full version of kgf and story

  the full version of kgf and story The teaser of KGF: Chapter 2 is scheduled to be released at the end of the month on Jan. 8.   Let's have a look at KGF Chapter 1 , and see what  audience can expect for the next installment. The winter of 2018-19 was the time of Yash as well as Rocky Bhai.   The tall, handsome star's nonchalant dialogues in the movie  KGF Chapter  1 were the talk of the town on social media. It was a defining moment in Indian cinematic history.   And we were not disappointed and even before the release of KGF: Chapter 1 there was an immense buzz about the film that was the most expensive Kannada film ever produced and was released in more than 1,800 theaters. KGF Chapter 1 delivered big time and was made into a massive blockbuster.   The film was an visually appealing masala  entertainment with exciting action sequences that were enthralling as well as a great dialogue, all accompanied by Yash's charisma, that all combined ...
Read more

random number generator

random --- Generate pseudo-random numbers The source code is Lib/random.py The module implements pseudoto implement random number generators for different distributions. For integers, there's a uniform selection of an array. For sequences there is uniform selection of an random element and it is a method to generate a random permutation of a list at-place and a program for random sampling without replacement. On the real line, there are tools to calculate normal, uniform (Gaussian), negatively exponential, gamma, and beta distributions. For the purpose of generating distributions for angles such as the von Mises distributive is accessible. Almost all module functions Most module functions rely on the core method random() that generates a random float with a uniform semi-open spectrum [0.0, 1.0). Python makes use of an algorithm called the Mersenne Twister as the core generator . It produces 53-bit precision floats and runs for 2**19937-1. The base implementatio...
Read more