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 preferred language usedin many computationsin Power BI, many do not realize this feature of Power Query. In this blog article I'm going to explain how easy it is to calculateAge in Power BI using PowerBI. It is a methodis extremely efficient in situations where your estimation of the agecan be performed on a pre-calculated row and row basis.

Calculate Age from a date

Below you can see the DimCustomer table that is part of the AdventureWorksDW table that has a birthdate column. I've removed a few of the columns that aren't needed to make it easier to read;

In order to calculate the actual age of each buyer, you need is:

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

That's it. this calculates the calculate an amount that is the sum of the Birthdate column, and the date and time.

But, the age appears in the Age column, doesn't really appear to be an age. This is because it's an actual Duration.

Duration

Duration is a unique kind of data format within Power Query which represents the differences between two DateTime values. Duration is a mixture of four values:

days.hours.minutes.seconds

and that's how you can interpret the numbers above. But from the viewpoint of the user it is not expected of them to read particulars like that. there are ways that you are able to get each segment of the duration. using the Duration menu option you'll notice that you can extract the amount of seconds, minutes, hours, days and years from it.

To help in calculating the age in years by way of example it is easy to click on Total Years:

The duration is calculated in days and was then divided in 365, to give you the annual value.

Rounding

In the end, no one declares your age in 53.813698630136983! They say 53, which is rounded down. You can select Rounding and Round Down from the Transform tab for it.

This will show you your age in years:

You can then clean the other columns, if desired (or it could be that you made use of transformations in the Transform tab to prevent making new columns) You can name this column; Age:

Things to Know

  • Refresh The data's age calculated this way will be refreshed at the time of refreshing your database. Every time, the system will be able to compare the birthdate to the date and the time during the process of refreshing. This method is an algorithm for pre-calculating the age. If you want the age calculation to be done dynamically using DAX here is how I described how to make use of.
  • The reason behind Power Query: Benefits of using age calculation with Power Query is that the calculation is made at the time of refreshing your report. This is done by using a tool that makes calculation much easier and faster, and there's no added cost in calculating it using DAX as a measure runtime.
  • Other scenarios These are not intended for the calculation of age from the birthdate. It can be used to calculate the age of inventory for products and also the differing dates and dates from 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 in Computer engineering. He has more than 20 years of experience in the field of data analysis, BI, databases, developing, and programming mostly with Microsoft technologies. He has been a Microsoft Data Platform MVP for nine continuous years (from 2011, until now) for his passion for Microsoft BI. Reza is a prolific author and co-founder at RADACAD. Reza is also the co-founder and co-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 wrote some books on MS SQL BI and also is working on other books. He was also an active member of online technical forums such as MSDN and Experts-Exchange and was a moderator of MSDN SQL Server forums, and is an MCP and MCSE as well as an MCITP in BI. He is also the leader for the New Zealand Business Intelligence users group. The group is also creator of the book that is very well-loved Power BI from Rookie to Rock Star, which is completely free and includes more than 1700 pages of content and an additional book called Power BI Pro Architecture published by Apress.
It 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 dream is to help users find the best solutions for data, and he's a Data enthusiast.This post was filed 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. The following is a great resource for you to bookmark.

Post navigation

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

Comments

Post a Comment

Popular posts from this blog

Peculiar Meaning In Tamil

  Peculiar Meaning In Tamil - தமிழ் பொருள் விளக்கம் " Peculiar " தமிழ் மொழிபெயர்ப்பு, பொருள், வரையறை, விளக்கம் மற்றும் தொடர்புடைய சொற்கள் மற்றும் புகைப்பட எடுத்துக்காட்டுகள் - நீங்கள் இங்கே படிக்கலாம். Peculiar   ♪ : /pəˈkyo͞olyər/ சொற்றொடர் : - பெயரடை : adjective வழக்கமற்ற கண்டு கேட்டறியாத புதிர் வாய்ந்த அவர்களுக்கு இடையே ஒரு விசித்திரமான பிணைப்பு இருந்தது அவள் நடனமாடும் போது மிகவும் வித்தியாசமான ஒன்றை கவனித்தால் அசாதாரண ஒற்றைப்படை தனிப்பட்ட சிறப்பு சிறப்பு ஒருவருக்கு மட்டுமே விசித்திரமானது தனிப்பட்ட அசாதாரணமானது அரிது சிறப்பு விசித்திரமானது விகாரமான விசித்திரமான தனியில்புக்கூறு சிறப்பியல்பு சிறப்புத்தனியுரிமை வட்டார மேலாட்சியிலிருந்து விலக்குரிமைபெற்ற திருச்சபை தனிச்சிறப்புமையுடைய திருக்ககோயில் சிறப்புரிமையான தனியியல்பான தனிப்பட்ட தன்மையுடைய பொது நீங்கிய தனி ஒருவர்க்கு மட்டுமேயுரிய தனி ஒன்றனுக்கேயுரிய தனிப்படக் குறிப்பிடத்தக்க தனித் தன்மைப்பட்ட பெயர்ச்சொல் : noun சலுகை சிறப்பு அம்சம் விசித்திரமானது அரிது சிறப்பு சொத...
Read more

Random Number Generators

Random number generation Random number generation is a process where, most often through the random number generator (RNG) or a random sequence of symbols or numbers that cannot be reasonably predictable better than random chance is generated. This means that the resultant sequence may contain patterns that are detectable in hindsight however unpredictable to the foresight. In reality, random number generators can be hardware random-number generators (HRNGS) that generate random numbers, and each generation is dependent on the current value of the physical attribute that is changing constantly in a way that's difficult to predict. This would be in contrast to so-called " random number generations" done by pseudorandom number generators (PRNGs) that generate numbers that only look random but are in fact pre-determined--these generations can be reproduced simply by knowing the state of the PRNG. The many applications of randomness have led to the invention ...
Read more

Convert Length and Distance Units Instantly

  LENGTH CONVERTER Select the length that you want to use, then enter the amount you'd like to convert to a different measurement of length within the "From" field, and select the measurement you'd like to convert it to in the "To" field.   The conversion results will be displayed on the "To" field right after you click on any unit. The term"length," which you've probably heard of , is defined by the term "length" as a precise  measurement  of everything from one end to the next.   It's difficult to locate exact measurements that convert miles into kilometers.   Sometimes, we need an estimate of how long it takes for others to get there or the amount of time it takes to get there and this is why length converters can help in calculations.   Measurement  Converter can be used in accordance with the needs since it converts any length type. It can help you with problems related to math and also.   To figure out the distance...
Read more