Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, since DAX is the most used language usedin several calculationin Power BI a lot of people do not know about this feature within Power Query. In this blog post , I will explain how easy to calculateAge in Power BI using PowerBI. This methodis highly beneficial in cases where the calculation of the agecan be done in a row by row basis.

Calculate Age from a date

This is the DimCustomer table from the AdventureWorksDW table, which includes an age column. I've removed some columns that aren't required to make it more user-friendly;

To calculate the exact age each purchaser, the following information is required:

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

That's about it. This calculates the calculate the amount which is the total of the Birthdate column as well as the current date and hour.

The age that appears within the Age column, doesn't seem to be an actual age. It is due to an actual length.

Duration

Duration is a different type of data that is utilized for Power Query which represents the distinction between two DateTime values. Duration is the result of four numbers:

days.hours.minutes.seconds

This is how you read the above information. But from someone else's viewpoint, you wouldn't need them to search for information such as this. There are methods that can find each part that represents an amount of time. When you go to the Duration menu , you'll find that you'll be able extract the amount of seconds or minutes or hours, days, and years out of it.

In order to aid with calculating the age in years such as, for instance, it is easy to select Total Years.

Make note of the fact that the duration of the program is calculated in days , then subdivided into 365 to provide you with the value for the year.

Rounding

Finally, no one says they're 53.813698630136983! They refer to it as 53, then they round it down. It's simple to select the Rounding option and Round Down in the Transform tab.

This will show you your age in years:

It is then possible to clean other columns if you'd like (or maybe you've made use of transformations within the Transform tab to prevent the making of columns) The column could be renamed to Age column or Age:

Things to Know

  • Refresh The age calculated by this method is updated every time you refresh your database. And each time it will check the birthdate with the date and date at which the refreshed. This method is a pre-calculation of the age. If you're in need of the calculation to be dynamically done by DAX, I have explained a way that you can apply.
  • The motivation behind Power Query: Benefits of performing age calculations in Power Query is that the calculation is carried out in the course of refreshing your report. You use an instrument that makes the calculation more efficient, and there's also no additional expense of using DAX to determine runtime.
  • Another scenario These are not meant to calculate age but rather start with the date of birth. This can be used to determine the date of inventory for products as well as for the variation in dates between 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 in Computer engineering. He holds greater than twenty years' experience in the fields of data analysis data and BI, database development and programming with a focus using Microsoft technologies. He has been an Microsoft Data Platform MVP for nine years in a row (from 2011, until now) in recognition of his commitment toward Microsoft BI. Reza can be found as an active writer and co-founder of RADACAD. Reza is also co-founder and co-organizer of 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 few works concerning MS SQL BI and also is writing a few others. In addition, he was a frequent participant in online forums for technical issues such as MSDN , and Experts-Exchange and was the moderator on the MSDN SQL Server forums, and is an MCP or MCSE. He also holds the MCITP for BI. He is the founder of the New Zealand Business Intelligence users group. Also, the writer of the popular guidebook Power BI from Rookie to Rock Star, which is free with more than 170 pages of material as well as being a component 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 find the right data solution. He's a Data enthusiast.This entry was written by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is classified in Power BI, Power BI from Rookie to Rock Star, Power Query. The following is apermalink.

Post navigation

Share visual pages on different security groups in Power BIAge Calculation in Years which can be used for calculate Leap Year in Power BI through Power Query

Comments

Post a Comment

Popular posts from this blog

Existing Meaning In Marathi

Image
   " Existing " मराठी अनुवाद, अर्थ, व्याख्या, स्पष्टीकरण आणि संबंधित शब्द आणि फोटो उदाहरणे - आपण येथे वाचू शकता. Existing   ♪ : /iɡˈzistiNG/ विशेषण : adjective विद्यमान असल्याचे आत आज विद्यमान प्रतिमा : Image स्पष्टीकरण : Explanation विचाराधीन वेळी अस्तित्वात किंवा ऑपरेशनमध्ये; चालू अस्तित्वात आहे, अस्तित्वात आहे स्वतःचे समर्थन करा सध्या अस्तित्वात आहे अस्तित्व किंवा अस्तित्व किंवा वास्तविकता असणे निर्दिष्ट काहीतरी अस्तित्वात Exist   ♪ : /iɡˈzist/ अकर्मक क्रियापद : intransitive verb अस्तित्वात तेथे तेथे उच्च जीवन राहतात मूल्य नाही उलाटाकू उलाटायरू जीवन लोक विशिष्ट परिस्थितीत सेट दिसणे कानापरू संज्ञा : noun जोपर्यंत आहे रहा आहे हजर रहा क्रियापद : verb राहतात रहा रहा विद्यमान रहा अर्ज करा राज्य करा Existed   ♪ : /ɪɡˈzɪst/ वाक्यांश : - विद्यमान विशेषण : adjective विद्यमान क्रियापद : verb विद्यमान द Existence   ♪ : /iɡˈzistəns/ वाक्यांश : - अस्ति...
Read more

counting in hindi numbers - (0 to 100)| Hindi Sankhya

  counting in hindi  numbers - (0 to 100)| Hindi Sankhya Hello, numbers are just an  mathematical concept .   They are used to measure all aspects of our lives.   But, they can also be written or spoken in a variety of languages. While a large portion of people are familiar by Language numbers, a lot of people aren't aware of Hindi numbers.   This is because Hindi numbers can be difficult to comprehend.   This article is intended for people who are of this kind. The country of India is the only country that uses Hindi numbers.   There are a few people who aren't conscious about Hindi numbers, yet aren't conscious about Language numbers. Counting of numbers in Hindi ko Hindi Sankhya Ginti kahthe hai. Learn here Hindi as well as English numbers 1 to 100.   (Hindi Sankhya Ginti - hiNdii sNkhyaa gintii).   So, without wasting a second , let's get to our primary topic: 1-100 Hindi  numbers in hindi  Counting Hindi numbers can be dif...
Read more

aadhaar update form

aadhaar update form /Address Change Form This is the application form for Address Change/update , issued by UIDAI. It is available for download from their website. https://uidai.gov.in/, or it can be obtained from the nearest Aadhaar Center or it can be directly downloaded from the link given below in PDF format for free. aadhaar update form /update form: Details to be included Types of Residents Aadhaar Number Full Name Current Address Change your address Name of the Certifiers Destination Address of the Office Any other information you would like to share Process for Change/ Update Address and Other Details Fill out this application form as the primary applicant. Include the evidence of an address change or other specifics. If the applicant does not have evidence of a change in address, he can certify the document to the following persons Gazetted Officer - Group A Village Panchayat...
Read more