Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way to calculating the age. But, as DAX is the preferred languagein several analysisin Power BI, many are not aware of the option available within Power Query. In this blog , I'm going to discuss how easy to calculateAge in Power BI by using Power BI. This methodis highly efficient when the age calculationcan be completed on a calculated row-by row basis.

Calculate Age from a date

The table is DimCustomer table in the AdventureWorksDW table, which has a birthdate column. I've removed a few of the extra columns for easier reading;

If you're trying to calculate the age of each customer all you have to do is to:

  • In Power BI Desktop, Click on Transform Data
  • In the PowerQuery Editor window; pick the Birthdate column first.
  • Click on the Add Column Tab. Under "From Date & Time" section, and under Date Choose the appropriate age range.

That's all there is to it. This will calculate an amount equal to the sum of the Birthdate column, together with the date and time of the present.

However, the age that you see under the Age column, doesn't appear to be an age. It's because it's a duration.

Duration

Duration is a distinctive data type that is found by using Power Query which represents the differences of the two DateTime values. Duration is a combination of four different values:

days.hours.minutes.seconds

And that's how you interpret the data above. However, from the viewpoint of users you don't want them to go looking for specifics like this. There are ways you can locate each of the parts in the length. When you select the Duration menu, it will display that you can determine the number of seconds or minutes, hours days, years, and seconds from it.

To make use of this method using the technique of calculating the age in years such as you simply choose Total Years.

Be aware that the duration that the programme runs is calculated in days . Then, it is divided by 365, to yield the annual amount.

Rounding

In the end, no one claims their years of age are 53.813698630136983! They refer to it as 53, with a rounding down. Select Rounding and Round Down on the Transform tab for it.

This will provide you with the age in years:

Then you can tidy the other columns, if desired (or maybe you've made a transformation via the Transform tab to avoid the creating columns) Also, name this column: Age:

Things to Know

  • Refresh The age that is calculated using this method is refreshed during the process that your data is refreshed. Each time, it will match your date of birth to the date and date on which the refresh of data took place. In this method can be used to calculate age earlier. If you require your calculation to be carried out by DAX in a way that is dynamically performed DAX Here's a detailed explanation of how to apply.
  • How to use Power Query The advantage of doing age calculations using Power Query is the fact that the calculation takes place in the course of updating your report. The calculation is made using an instrument that makes the calculation more simple, and there won't be any additional overhead when calculating it using DAX for determining of runtime.
  • Additional scenarios an accurate method of calculating age by the date of birth. This is a useful method to establish inventory levels for products and for the differences between different dates each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering. There are more than 20 years' experience in data analysis database, BI, programming and development that is primarily using Microsoft technologies. He has been a certified Microsoft Data Platform MVP for nine consecutive years (from 2011 till now) for his passion for Microsoft BI. Reza has been a prolific author and co-founder of RADACAD. Reza is also co-founder as well as coordinator of Difinity conference 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 several book concerning MS SQL BI and also is working on several additional. He was also an active participant in online forums for technical issues like MSDN and Experts Exchange. He was the moderator for MSDN SQL Server Forums, with the MCP as well as the MCSE. He also holds an MCITP for BI. He is the leader of the New Zealand Business Intelligence users group. Additionally, he's the writer of the book loved by many. Power BI from Rookie to Rock Star, which is gratis and contains greater than 700 pages of content as well as The Power BI Pro Architecture published by Apress.
Speakers are an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, in addition to SQL user groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help users to find the most efficient data solution. He is an avid Data enthusiast.This report was released with the title Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. This article is a good source to bookmark.

Post navigation

Share different visual pages using different security groups in Power the BIAge's age Calculation which can be used to calculate Leap Year within Power BI with Power Query to calculate Leap Year.

Comments

Post a Comment

Popular posts from this blog

Calorie Calculator

What is Calorie Counting? Calories are important , but they're certainly not required for every person! What Is Calorie Counting? Calorie counting is the method of recording the calories (or Kilojoules) quantity of each beverage and meal consumed daily. You can do it cooking with the journal app or by entering your data into an Excel spreadsheet. Alternatively, meal plans that incorporate calories can be planned ahead of time and followed to the letter. Do Calories Count? There are two views on thissubject: Calories and calories count as the sole thing that counts and have to be counted. Calories do not matter - it's the macronutrients (i.e. proteins, carbs, and fats) that are vital for everything. Concerning the consumption of energy and output calories are everything. As for health and nutrition the macronutrients play a role. Consider this In the event that you choose to adhere to an 1800-calorie a day diet - do you want to drink th
Read more

Convert octal to decimal:

Image
Introduction: podcasters often convert octal to decimal in order to make their calculations more efficient. In this short guide, we’ll show you how to do just that. Convert octal to decimal. An octal number is a number that represents 8 digits. It is created by doubling the value of a number that is either an int (1, 2, 3, 4, 5, 6...), or a float (0.01, 0.02, 0.03, 0.04, 0.05,...). To convert an octal number to decimal: To convert an octal number to decimal: There are two ways to do this: The first way is to use theOctetToDecimal function: $ echo "8" | OctetToDecimal This converts the octal value 8 into a decimal value of 100. How to Use the Octal to Decimal Conversion Chart. To convert an octal number (8-bit code) to a decimal number, divide the octal number by 2.5. For example, the value of 42 would be converted to decimal form as follows: 42 / 2.5 = 10 How to Conversion Octal Numbers to Decimal Numbers. To convert octal numbers to decimal numbers, divide the number by 10.
Read more

Hodophile Meaning In Hindi

   Hodophile Meaning In Hindi – हिंदी अर्थ व्याख्या “ Hodophile ” हिंदी अनुवाद, अर्थ, परिभाषा, स्पष्टीकरण और प्रासंगिक शब्द और फोटो उदाहरण – आप यहां पढ़ सकते हैं। Table of Contents Hodophile ♪ : [ Hodophile ] संज्ञा : noun किसी को जो यात्रा करने के लिए प्यार करता है व्याख्या : Explanation हिंदी की परिभाषा जल्द ही जोड़ दी जाएगी
Read more