Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, since DAX is the most popular language usedin many calculationsin Power BI, many do not know this function offered by Power Query. In this blog , I'm going to explain how easy to calculateAge in Power BI using PowerBI. It is a methodis extremely efficient when the estimation of your agecan be calculated on a pre-calculated row or basis.

Calculate Age from a date

Below you can view the DimCustomer table, which is part of the AdventureWorksDW table. The table has a birthdate column. I've removed a few of the columns that don't need to be in order to make it easier for you to understand;

To calculate the actual the age for each purchaser, you'll need:

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

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

However, the age that appears to be in the Age column, it doesn't actually seem to be an actual age. This is due to the fact that it's an actual duration.

Duration

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

days.hours.minutes.seconds

and that's how you see the numbers above. From the perspective of the user it is not expected of them to read particulars like that. There are methods that are able to obtain every part of the duration. using the Duration menu option there is a way to determine the number of seconds as well as minutes, hours days and years from it.

For assistance in calculating the age in years using an example, it is simple to select Total Years:

The duration was calculated in days . Then, it was divided into 365, which will yield the value of the year.

Rounding

It's the truth, nobody says your age is 53.813698630136983! They use 53 which is reduced to a lower number. You can select Rounding as well as Round Down in the Transform tab.

This will reveal how old you are:

You can then clean the other columns, if you want (or this could mean that you utilized transformations under the Transform tab to prevent making new columns) The column can be named column as Age:

Things to Know

  • Refresh The age of the data calculated in this manner will be refreshed at the time of refreshing your database. Every time it is refreshed, the system will be competent to match the birthdate to the date and the time in the process of refreshing. It is a method involves an algorithm to calculate the age. If you would like the calculation of age to be performed dynamically using DAX here's how I described how to make use of.
  • The reasoning behind Power Query: Benefits of using age calculations with Power Query is that the calculation is done when you refresh your report. It is accomplished by using an application that makes the calculation much easier and faster, as well as there is no cost in the calculation using DAX because it is a way to gauge of runtime.
  • Other scenarios They aren't intended for the calculation of age from birth date. It is possible to calculate the time of inventory on products and also to determine the differences in 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 holds a BSc with a major degree in Computer engineering. More than twenty years' experience in the field of data analysis data, BI, databases designing, and programming primarily with Microsoft technologies. He was a Microsoft Data Platform MVP for nine years in a row (from 2011 until today) in recognition of his love for Microsoft BI. Reza has been a prolific writer and co-founder of RADACAD. Reza is also the co-founder and organizer of the Difinity event 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 also wrote a few books about MS SQL BI and also is working on different books. He was also an active forum member on online technical forums such as MSDN as well as Experts-Exchange and was moderator for the MSDN SQL Server forums as well as an MCP, MCSE and as an MCITP in Business Intelligence. He is also the leader for the New Zealand Business Intelligence users group. The group is also creator of the book highly praised Power BI from Rookie to Rock Star, which is completely free and includes over 1700 pages of information and a second book titled Power BI Pro Architecture published by Apress.
This is an International Presenter 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 goal is to help users discover the most effective solutions for data, and He's a Data enthusiast.This post was filed into Power BI, Power BI from Rookie to Rockstar, Power Query and is listed under Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic resource for you to bookmark.

Post navigation

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

Comments

Post a Comment

Popular posts from this blog

shiv chalisa pdf

  Shiv Chalisa Lyrics | श्री शिव चालीसा अर्थ ( shiv chalisa pdf ) श्री शिव चालीसा पढ़ने से पहले दोहा पढ़ा जाता है जो इस प्रकार है। ।। दोहा ।। श्री गणेश गिरिजा सुवन, मंगल मूल सुजान। कहत अयोध्यादास तुम, देहु अभय वरदान॥ हे गिरिजा पुत्र भगवान श्री गणेश आपकी जय हो। आप मंगलकारी हैं, विद्वता के दाता हैं, अयोध्यादास की प्रार्थना है प्रभु कि आप ऐसा वरदान दें जिससे सारे भय समाप्त हो जांए। || श्री शिव चालीसा चौपाई || जय गिरिजा पति दीन दयाला। सदा करत सन्तन प्रतिपाला॥ भाल चन्द्रमा सोहत नीके। कानन कुण्डल नागफनी के॥ अर्थ : हे गिरिजा पति हे, दीन हीन पर दया बरसाने वाले भगवान शिव आपकी जय हो, आप सदा संतो के प्रतिपालक रहे हैं। आपके मस्तक पर छोटा सा चंद्रमा शोभायमान है, आपने कानों में नागफनी के कुंडल डाल रखें हैं। अंग गौर शिर गंग बहाये। मुण्डमाल तन छार लगाये॥ वस्त्र खाल बाघम्बर सोहे। छवि को देख नाग मुनि मोहे॥ अर्थ : आपकी जटाओं से ही गंगा बहती है, आपके गले में मुंडमाल (माना जाता है भगवान शिव के गले में जो माला है उसके सभी शीष देवी सती के हैं, देवी सती का 108वां जन्म राजा दक्ष प्रजापति...
Read more

BMI calculator

BMI Calculator Knowing BMI can help both men and women understand how they are doing in the overall. Make use of BMI calculator beneath to calculate the body mass index of your. BMI calculator in the following table to determine your BMI by entering your height and weight. The BMI calculator utilizes the following BMI formula in order to determine the BMI: Weight (lb) + (Height (in))2 703. Understanding Your Body Mass Index If your BMI is lower than 18.5: Your BMI is considered to be in the category of overweight. Be aware that an overweight BMI calculation could lead to risk to your health. Talk to your doctor for more details on BMI calculation. When your BMI is within an area between 18.5-24.9: Your BMI is considered to be normal. A healthy weight will reduce the chance of developing severe health issues , and it also indicates that you're close to reaching your goals for fitness. In the event that your BMI is in the range of 25-29.9: Your BMI will be ...
Read more

What is Calorie Counting?

What is Calorie Counting? Calories are counted, but not everyone needs to count them! What Is Calorie Counting? Calorie counting is the method of recording the calories (or Kilojoule) quantity of every drink and food item you consume each day. It is possible to do this using the Food journal or app, or simply by recording the data on an Excel spreadsheet. Alternatively, calorie-based meal plans may be planned in advance and followed precisely. Do Calories Count? There are two major opinions regarding this issue: Calories and calories are the only thing that matters and should be measured. Calories aren't important - it's those macronutrients (i.e. carbohydrates, protein, fats) which are the most important. In terms of the energy intake and output, calories are everything. In terms of nutrition and health macronutrients are important. Consider this If you were to follow an 1800 calories per day diet, would you take all of...
Read more