Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way in calculating the age. However, as DAX is the most popular language usedin several calculationsin the Power BI platform, many do not know about the function that is available in Power Query. In this blog , I will describe how simple to calculateAge in Power BI with Power BI. The methodis extremely useful for situations in which the computation of an agecan be conducted on a previous calculated row by row basis.

Calculate Age from a date

That's the DimCustomer table that is part of the AdventureWorksDW table that has the birthdate column. I've removed some columns that aren't necessary in order to make it easier to read;

For you to calculate the exact age each buyer, you need is:

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

That's all there is to it. this calculates the calculate an amount that is the sum of the Birthdate column, along with the current date and the time.

But, the age that is displayed in the Age column, doesn't seem to be an actual age. That is because it is an actual duration.

Duration

Duration is a distinct form of data used by Power Query which represents the differences between the two DateTime values. Duration is the result of four numbers:

days.hours.minutes.seconds

This is the way you interpret the above statements. But, from someone else's viewpoint, you wouldn't want them to find data like this. There are methods that can extract each segment that's what you need to know about time. When you select the Duration menu you'll discover that you can extract the amount of seconds or minutes or hours, days, and years from it.

to assist with calculating the age in years like, for instance it is simple to choose Total Years.

Note that the length for the course is calculated in days , then subdivided into 365 so that you can come up with the annual cost.

Rounding

Finally, no one says they are 53.813698630136983! They use the term 53, and then then round it down. It's easy to select rounding as well as Round Down in the Transform tab.

This will give you the number in years:

It's also possible to cleanse other columns if you'd like (or perhaps you've taken advantage of transformations using the Transform tab to stop the creating of additional columns) The column could be renamed to Age column and Age:

Things to Know

  • Refresh The age that is calculated by this method will be updated every time you refresh your database. Every time, the database will be compared the birthdate to the date and the date at which the refreshed. This method is an earlier calculation of the age. If you're looking for this calculation run dynamically using DAX, here I provided a method you could use.
  • The rationale for Power Query: Benefits of performing age calculations in Power Query is that the calculation is carried out while you refresh the report. The report is refreshed using a tool that makes the calculation more efficient, and there's no cost of doing it with DAX to gauge the runtime.
  • Another case where these are not in use to calculate age, only beginning from the birth date. It could be used to calculate the age of inventory for items and also to determine the differences 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 a BSc of Computer engineering. He holds the more than 20 years' knowledge in the field of data analysis and BI, database development and programming, mostly based on Microsoft technologies. He has been a Microsoft Data Platform MVP for 9 consecutive years (from 2011, until now) for his commitment toward Microsoft BI. Reza has been an active writer and co-founder of RADACAD. Reza is co-founder and co-organizer for the Difinity Conference which is 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 has written a couple of book about MS SQL BI and also is working on more books. He was also a frequent participant on technical forums online such as MicrosoftDN as well as Experts-Exchange and was the moderator for MSDN SQL Server forums, and holds the MCP as well as MCSE and MCITP for Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. Also, the creator of the famous publication Power BI from Rookie to Rock Star, which is free with more than 170 pages of content as well as it is a portion of Power BI Pro Architecture published by Apress.
It is an International Speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's passion is to help users discover the most efficient solutions for their data, and he's a Data enthusiast.This article was published within Power BI, Power BI from Rookie to Rockstar, Power Query and is listed into Power BI, Power BI from Rookie to Rock Star, Power Query. You can follow any comments to this entry through the RSS feed.

Post navigation

Create different visual pages and share them with different security groups in Power BIAge in Years Calculation that can be used as a way to calculate Leap Year in Power Business Intelligence by using Power Query

Comments

Post a Comment

Popular posts from this blog

BMI calculator

Understanding your BMI Calculating your BMI by using this Body Mass Index calculator or manually is just the beginning. But understanding that it is an important step. For more details these are the categories that your health issue could be classified into depending on your BMI or BMI. In the event that your BMI is below 18.5 - Your health condition would be classified as being overweight with comparison to other people of the same age, gender, and height. When your BMI is greater than 18.5 and Your physical health status could be considered healthy in relation to the individuals with similar gender or age, as well as height and weight. If your BMI is greater than 25, but 29.9 The health problem you are experiencing could be classified as overweight in comparison to people who have the same gender, age, and height as you. If you're BMI is > 30 The health conditions you suffer from could be classi...
Read more

hmm full form is hidden Markov Model

  hmm full form  is hidden Markov Model HMM is the abbreviation for hidden  Markov  Model.   HMM is an statistical Markov model where the system that is being studied is believed to be an actual Markov process, or X and has non-observable (or obscured) states. hmm full form is Hyundai Merchant Marine HMM is the abbreviation that stands for Hyundai Merchant Marine.   HMM is a container shipping business that was founded in the year 1976 within Seoul,  South Korea , and it is active worldwide.   For those who are fluent in the Hangul language, the word is eiciemem.   It is the national top-ranked company in the nation. HTML0hmm's full version includes Hallmark Movies & Mysteries "HMM (  meaning of hmm in Whatsapp )long-form refers to Hallmark Movies & Mysteries.   It was previously known as Hallmark Movie Channel.   Crown Media Holdings is the owner of this American digital satellite and cable television channel.   I...
Read more

Vinegar Meaning In Marathi

  Feeding Meaning In Hindi - हिंदी अर्थ व्याख्या "Feeding" हिंदी अनुवाद, अर्थ, परिभाषा, स्पष्टीकरण और प्रासंगिक शब्द और फोटो उदाहरण - आप यहां पढ़ सकते हैं। Feeding   ♪ : /fiːd/ मुहावरा : - खाद्य दलाल संज्ञा : noun खाना Accatiyantrattilekk कागज नियमित रूप से nikkikkeatukkal माल क्रिया : verb दूध पिलाने की खाना Tirrutal भोजन Eriporuluttutal अवा की पूर्ति चरागाह फीडर प्रिंट के लिए तैयार करें कागज आदेश के साथ रखा। kalimeykkal व्याख्या : Explanation को भोजन दें। (विशेष रूप से एक जानवर या बच्चे के) भोजन लेना; कुछ खा लो। किसी व्यक्ति या जानवर को बड़ी मात्रा में भोजन दें। के लिए भोजन की पर्याप्त आपूर्ति प्रदान करें। (एक विशेष पदार्थ) से नियमित पोषण प्राप्त करें उर्वरक एक पौधे में दें) (आग) पर ईंधन रखो। की वृद्धि को प्रोत्साहित करें। संतुष्ट (एक दवा की आदत) सामग्री या शक्ति के साथ आपूर्ति। एक मशीन में डाल दिया। उस समय का विस्तार करने के लिए (एक मीटर) में और सिक्के डालें, जिसके लिए वह संचालित होता है। आपूर्ति (किसी के साथ) (सूचना,...
Read more