Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, since DAX is the preferred language usedin many computationsin Power BI, many do not realize this feature that is available in Power Query. In this blog post, I'll go over how easy it is to calculateAge in Power BI with PowerBI. This methodis extremely efficient in situations where your estimation of the agecan be calculated using a pre-calculated row the row basis.

Calculate Age from a date

Below is the DimCustomer table, which forms part of the AdventureWorksDW table that has a birthdate column. I've removed several of the columns that aren't necessary in order to make it easier for you to be read

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

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

That's it. This calculates the calculate the amount which is the sum of the Birthdate column, along with the date and time.

The age, however, as it appears on the Age column, it doesn't actually seem to be an actual age. It's because it's an actual Duration.

Duration

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

days.hours.minutes.seconds

That's the way you interpret the numbers above. From the perspective of the user it is not their responsibility to read particulars like that. There are methods to ensure that you can get every portion of the duration. Utilizing the Duration menu option, you'll see that you are able to get the duration in seconds, minutes, hours, days and years from it.

To aid in calculating the age in years through an example, it is easy to click on Total Years:

The duration is calculated as days and divided in 365 to provide you with the annual value.

Rounding

It's the truth, no one says your age is 53.813698630136983! They say 53, which is reduced to a lower number. It is possible to select Rounding and Round Down from the Transform tab for it.

This will reveal how old you are:

It is then possible to clean other columns, if you want (or this could mean that you made use of transformations on the Transform tab to prevent making new columns) This column can be named column: Age

Things to Know

  • Refresh The information's age calculated through this method will be refreshed at the time of refreshing your database. Each time, the system will be capable of comparing the birth date to the date and timing of the refreshing. The method will be an algorithm for pre-calculating the age. If you would like your age calculation to be done in a dynamic manner using DAX Here is the procedure I explained what you can make use of.
  • The reasoning behind Power Query: Benefits from using age calculation in Power Query is that the calculation takes place during the process of refreshing your report. This is accomplished using a tool that makes calculation easy and quicker, and there's no added cost in calculating it using DAX in order to determine of runtime.
  • Other scenarios are not meant to be used for calculation of age from birth date. It is possible to calculate the age of inventory for products and also the various 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 has an BSc with a concentration with a concentration in Computer engineering. There are more than 20 years work experience in the field of data analysis data, BI, databases creating, and programming mostly using Microsoft technologies. He was an Microsoft Data Platform MVP for nine consecutive years (from 2011 until the present) in recognition of his love for Microsoft BI. Reza is known as a prolific writer and co-founder of RADACAD. Reza is also co-founder of 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 has written books on MS SQL BI and also is working on various other books. He was also a regular member of online technical forums like MSDN as well as Experts-Exchange and was moderator for the MSDN SQL Server forums as well as an MCP and MCSE , as well as an MCITP in BI. He is also the head of the New Zealand Business Intelligence users group. The group is also the author of the book extremely well-loved Power BI from Rookie to Rock Star, which is entirely free and comprises over 1700 pages of material and a book that is titled Power BI Pro Architecture published by Apress.
There is an International Speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user group. And He is a Microsoft Certified Trainer.
Reza's ambition is to help users find the best solutions for data, and He's an avid 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 source to bookmark.

Post navigation

Share Different Visual Pages through different Security Groups. PowerBIAge in Years Calculation that works for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

BMI calculator

BMI Calculator Knowing BMI helps both adult women and men to understand what they are doing overall. Utilize the BMI calculator in the following table to determine your BMI. BMI calculator Below to determine your body mass index by entering your height and weight. The BMI calculator employs the following BMI formula in order to determine the following: Weight (lb) and (Height (in))2 x 703. Understanding Your Body Mass Index If your BMI is below 18.5: Your BMI is thought to fall into the category of overweight. It is important to be aware of the fact that an underweight BMI calculation can pose health hazards. Talk to your doctor for further details about BMI calculations. When your BMI is within the area of 18.5-24.9: Your BMI is considered to be normal. Healthy weight levels can decrease the chance of developing serious health issues , and it also indicates that you're on the verge of achieving what you want to achieve in fitness. In the event that your BMI ...
Read more

Query Meaning In Marathi - मराठी अर्थ स्पष्टीकरण

   " Query " मराठी अनुवाद, अर्थ, व्याख्या, स्पष्टीकरण आणि संबंधित शब्द आणि फोटो उदाहरणे - आपण येथे वाचू शकता. Query   ♪ : /ˈkwirē/ संज्ञा : noun प्रश्न वीणा प्रश्न व्यत्यय आणणे क्वेरी चौकशी करणे चौकशी प्रश्न शंका समस्या चाचणी चे चिन्ह लालसा चौकशी समस्या चिन्ह संभाषण मोडमध्ये संगणक वापरताना सर्व्हर किंवा डेटाबेसची विनंती करा क्रियापद : verb प्रश्न विचारा प्रश्न चिन्ह विचारा विचारा शंका प्रश्न स्पष्टीकरण : Explanation एक प्रश्न, विशेषत: एक अधिकारी किंवा संस्थेला उद्देशून. खालील विधानाच्या अचूकतेवर प्रश्न विचारण्यासाठी किंवा प्रश्न ओळखण्यासाठी लेखी किंवा बोलण्यात वापरला जातो. एक प्रश्नचिन्ह. एखाद्याबद्दल याबद्दलचे प्रश्न विचारा, विशेषत: एखाद्याबद्दल त्याबद्दल शंका व्यक्त करण्यासाठी किंवा त्याची वैधता किंवा अचूकता तपासण्यासाठी. (कोणास) एखादा प्रश्न किंवा प्रश्न द्या प्रश्न एक उदाहरण एक प्रश्न विचारू Queried   ♪ : /ˈkwɪəri/ संज्ञा : noun चौकशी केली प्रश्न Queries   ♪ : /ˈkwɪəri/ संज्ञा : noun ...
Read more
Calculator Components If you've read the first page, then you're aware by this point that portable calculators need single-chip microprocessors to perform their functions. But how do you activate the microprocessor? It all starts with the information located on the outside of your device. The majority of modern calculators feature a sturdy plastic casing with small holes in the front to allow rubber to pass through, similar to a TV remote. By pressing a button, you complete a circuit underneath the rubber. It sends electrical impulses across a circuit board beneath. Those impulses are routed through the microprocessor, which processes the information and sends an output to the calculator's display screen. The displays in the early electronic calculators comprised of LEDs or light-emitting diodes . The latest models that require less power feature a the liquid crystal display or LCD. Instead of producing light LCDs alter light molecules to create a patter...
Read more