Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way for calculating the age. However, because DAX is the popular languagein many calculationsin Power BI, many are not aware of the function available in Power Query. In this article I'll describe how simple it is to calculateAge in Power BI with Power BI. This methodis extremely beneficial when the estimation of the agecan be carried out on an already calculated row-by-row basis.

Calculate Age from a date

This is the DimCustomer table from AdventureWorksDW table, which is an age column. I've removed a few of the extra columns to make it easier to read;

To calculate an age range for each person who is a customer, all that you have to do is:

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

That's it. This calculates the calculate the difference between the Birthdate column and the date and time.

However, the age appears under the Age column, doesn't appear like an age. It's because it's a Duration.

Duration

Duration is a special kind of data format found in Power Query which represents the distinction of two DateTime values. Duration is a mix of four numbers:

days.hours.minutes.seconds

and that is how you read the values above. But from an individual's perspective it is not expected of them to be able to access the particulars of the ones above. There are methods that can get each portion of the time. When you select the Duration menu You will notice that you are able to extract the number of seconds to minutes, hours days and years out of it.

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

The duration is calculated in days , and was then divided in 365, to calculate the value for the year.

Rounding

It's the truth, no one claims their age as 53.813698630136983! They say 53, with a rounding down. You can select Rounding and Round Down from the Transform tab.

This will provide you with the age in years:

It is then possible to clean other columns if you'd like (or there is a chance that you have utilized transformations in the Transform tab to prevent having to create new columns) You can name this column Age.

Things to Know

  • Refresh The age that is calculated by this method will be updated every time you are refreshing your database. Every time it will check your birthdate with the date and date that the data refresh took place. This method is a pre-calculation of the age. If, however, you require the calculation of age to be done dynamically using DAX This is the way I've explained the method you could use.
  • The reason behind Power Query: Benefits of performing an age calculation using Power Query is that the calculation takes place at the time of refreshing your report, using an instrument that makes the calculation much easier and faster, and there's no added cost in calculating it using DAX to measure runtime.
  • Additional scenarios This is not a method to calculate age just from birthdate. This can be used to calculate inventory of products as well as the different between two dates or dates from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has been awarded a BSc from Computer engineering. He have more that 20 years' experience in the field of data analysis database, BI, programming, and development mostly in Microsoft technologies. He is an official Microsoft Data Platform MVP for nine consecutive years (from 2011, until now) for his passion for Microsoft BI. Reza is an active writer and is co-founder with RADACAD. Reza is also co-founder as well as co-organizer of the Difinity the conference held 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 wrote several books on MS SQL BI and also is writing a few more. He was also an active member of online technical forums like MSDN and Experts-Exchange and was also the moderator of MSDN SQL Server forums, and holds the MCP, MCSE, and MCITP of Business Intelligence. He is the head of the New Zealand Business Intelligence users group. Additionally, he is the creator of the book that is very well-loved Power BI from Rookie to Rock Star, which is free and contains more than 700 pages of content, as well as it is a part of 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 group for users. And He is a Microsoft Certified Trainer.
Reza's passion is to help users find the best solutions for data, and he's a Data enthusiast.This post was filed by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and tagged Power BI, Power BI from Rookie to Rock Star, Power Query. Bookmark the permalink.

Post navigation

Share Different Visual Pages using different security groups in Power BIAge's Age Calculation which works for Leap Year in Power BI with Power Query

Comments

Post a Comment

Popular posts from this blog

Niece Meaning In Kannada - ಕನ್ನಡ ಅರ್ಥ ವಿವರಣೆ

  Niece Meaning In Kannada - ಕನ್ನಡ ಅರ್ಥ ವಿವರಣೆ " Niece " ಕನ್ನಡ ಅನುವಾದ, ಅರ್ಥ, ವ್ಯಾಖ್ಯಾನ, ವಿವರಣೆ ಮತ್ತು ಸಂಬಂಧಿತ ಪದಗಳು ಮತ್ತು ಫೋಟೋ ಉದಾಹರಣೆಗಳು - ನೀವು ಇಲ್ಲಿ ಓದಬಹುದು. Niece   ♪ : / nēs / ನಾಮಪದ : noun ಸೋದರ ಸೊಸೆ ಸಹ-ಜನಿಸಿದ ಮಗಳು ಸಹೋದರನ ಮಗಳು ಅಳಿಯನನ್ನು ಹೊಂದಲು ಸೋದರ ಮಾವ (ಎ) ಅತ್ತಿಗೆ ತಂಗಿ ಸಹೋದರಿಯ ಮಗಳು ಮಗಳು ಒಡಹುಟ್ಟಿದವರ ಮಗಳು ದಮಯನ್-ತಮಾಬಿ-ತಮಾಕೈ-ಸಹೋದರಿಯ ಮಗಳು ಸೋದರ ಮಾವ (ಎ) ಸಹೋದರಿಯ ಮಗಳು ಸಹೋದರ ಅಥವಾ ಸಹೋದರಿಯ ಮಗಳು ഭാഗിനേയി ಸೋದರ ಸೊಸೆ ಸೋದರ ಸೊಸೆ ಸಹೋದರಿಯ ಮಗಳು ಚೆಚಿಯ ಮಗಳು ವಿವರಣೆ : Explanation ಒಬ್ಬರ ಸಹೋದರ ಅಥವಾ ಸಹೋದರಿಯ ಮಗಳು, ಅಥವಾ ಒಬ್ಬರ ಸೋದರ ಮಾವ ಅಥವಾ ಅತ್ತಿಗೆ. ನಿಮ್ಮ ಸಹೋದರ ಅಥವಾ ಸಹೋದರಿಯ ಮಗಳು Nieces   ♪ : /niːs/ ನಾಮಪದ : noun ಸೊಸೆಯಂದಿರು ಸಹೋದರ ತಂಗಿ ಸಹೋದರಿಯ ಮಗಳು ಹೆಣ್ಣುಮಕ್ಕಳೊಂದಿಗೆ
Read more

power-converter

Image
What is An angle ? Acute, obstuse or narrow. An angle is a shape formed by two rays, which share an initial point, referred to as a vertex . Some may wonder why are angles useful? You can determine the height of the building when you know the distance from the building, and angles between the sides and the top. With the same technique you can figure out how big the moon is and, if you have the right tools to calculate your radius on our planet. Also, if your intention to throw something that you want to know how far it's going to travel you must determine the angle that will be at the point the object is thrown at. There are many disciplines where angles are vital, however we'll go over the basics of geometry. Angles are classified based on their size: WHAT IS A FREQUENCY CONVERTER ? Its frequency converter is commonly referred to as a power frequency converter . It's a device that receives the energy of an incoming source, typically 50 or 60Hz. It then converts...
Read more

Age Calculator

Exact Age Calculator It is an internet-based age calculator that allows you to calculate easily exactly your age. An individual's age can be measured in different ways across different societies. This calculator is based on the most common age system. In this age calculator it is all you have to pick which birthday then after which you can leave the rest to the calculator. The results provided through this age calculator is fully accurate. In this model, age increases at birth. For example, the age of someone who is alive for 3 years and 11 months is 3 years and his age will be 4 years old on his next birthday after a month. In certain situations the numbers of months and day calculated by using this age calculator google can be confusing, especially when the starting date is the last day of a month. For example it is common to count February. Exact Age Calculator There is an online age calculator which lets you quickly determine the exact age. A person...
Read more