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 preferred language usedin many calculationsin Power BI, many do not understand this capability offered by Power Query. In this article, I'll describe how easy to calculateAge in Power BI with PowerBI. This methodis extremely efficient when your estimation of your agecan be based on a pre-calculated row or rows basis.

Calculate Age from a date

Below, you will see the DimCustomer table, which forms part of the AdventureWorksDW table that has the birthdate column. I've removed several of the columns which aren't needed in order to make it easier for you to look at;

In order to calculate the the age for each purchaser, you'll need:

  • In Power BI Desktop, Click on Transform Data
  • In Power Query Editor window; start by selecting the column titled Birthdate.
  • Go to the Add Column Tab, then select"Add Column Tab," then click on the "From Date & Time" section. Under Date select the date range.

That's all there is. this calculates the calculate the amount which is the total of the column for birthdate, Birthdate column, as well as the date and time.

But, the age appears within the Age column, doesn't really appear to be an age. This is due to the fact that it's an actual duration.

Duration

Duration is a special kind of data format used in Power Query which represents the differentiating factors between the two DateTime values. Duration is a combination of four values:

days.hours.minutes.seconds

This is how you interpret the numbers above. But from the viewpoint of the user, it's not expected of them to know the specifics of that. there are ways that you can access each portion of the duration. When you select the Duration menu there is a way to extract the amount of seconds, minutes, hours, days and years from it.

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

The duration is calculated in days . It was then divided by 365 to give you the annual value.

Rounding

It's the truth, nobody proclaims their age in 53.813698630136983! They say 53, which is rounded down. It is possible to select Rounding and Round Down on the Transform tab for it.

This will let you know your age in years:

Then, you can clean the other columns, should you like (or it could be because you applied transformations in the Transform tab, to prevent creating new columns) The column can be named column Age:

Things to Know

  • Refresh The data's age determined this way will be refreshed when you refresh your database. Every time, the system will be capable of comparing the birth date to the date and duration of refreshing. That method can be described as an algorithm to predict the age. If you'd like the calculation of age executed dynamically by using DAX here is how I explained the best way to make use of.
  • The motivation behind Power Query: Benefits of using age calculations with Power Query is that the calculation is made during the process of refreshing your report. This is made possible by an application that makes the calculation much easier and faster, and there's no additional cost when calculating it with DAX as a measure of runtime.
  • Other scenarios intended for the calculation of age from birth date. It can be used to calculate the time of inventory on products and also to determine the differences in dates and dates with respect to each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc with a major degree in Computer engineering. There are more than 20 years experience in the fields of data analysis as well as databases, BI designing, and programming primarily using Microsoft technologies. He was a Microsoft Data Platform MVP for nine consecutive years (from 2011, to now) because of his love for Microsoft BI. Reza is also a prolific writer and co-founder of RADACAD. Reza is also the co-founder and co-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 has written a few books on MS SQL BI and also is writing various other books. He was also an active member of technical forums online like MSDN and Experts-Exchange and was moderator on the MSDN SQL Server forum He is also an MCP and MCSE as well with an MCITP in BI. He is also the leader of the New Zealand Business Intelligence users group. They are also the authors of the book highly praised Power BI from Rookie to Rock Star, which is entirely free and comprises greater than 1700 page of content as well as a companion book called 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 group for users. And He is a Microsoft Certified Trainer.
Reza's dream is to help users find the right options for data. is 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. This is a great guide to bookmark.

Post navigation

Share Multiple Visual Pages by using different Security Groups. PowerBIAge in years calculation that is used for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

Bachelor of Engineering

B. Tech is a four-year undergraduate program for undergraduates. After graduation, the student receives an engineering degree. This program is also known in the form of BE (or. Bachelor of Engineering or Engineering . B. Tech is a technological course that provides students with knowledge about their favorite fields.Full form of b.tech Students who are interested in the career of an engineer should take the B. Tech course. Engineering is about doing something new. If you're interested in engineering, you may want to take a look at the B. Tech course. This course teaches you all technical aspects of your field by using both practical and theoretical methods. This is the criteria for a student who wishes to pursue a B.Tech. Minimum Qualification 10+2 in Physics, Chemistry, and Maths Subjects For most quality colleges, the minimum percentage of +2 or intermediate should not exceed 50. Students who have successfully completed a diploma or polytec...
Read more

Crush Meaning In Tamil

Image
  " Crush " தமிழ் மொழிபெயர்ப்பு, பொருள், வரையறை, விளக்கம் மற்றும் தொடர்புடைய சொற்கள் மற்றும் புகைப்பட எடுத்துக்காட்டுகள் - நீங்கள் இங்கே படிக்கலாம். Crush ♪ : /krʌʃ/ பெயர்ச்சொல் : noun ஈர்ப்பு பரபரப்பு நசுக்கு கசக்கி அகற்றல் கூட்டம் பரபரப்பு நசுக்கு கசக்கி தியானம் கசக்கி தற்காலிக காதல் மாயத்தோற்றம் வினை : verb அரைக்கவும் அழிக்கவும் கிளிக் செய்க கசக்கி தேய்க்கவும் அரைக்கவும் நொறுக்கு அடக்கு உடைக்க கசக்கி வேலைநிறுத்தம் அச்சகம் சொற்றொடர் : verb and noun நசுக்குதல், கூட்டம், அழுத்துதல், நசுக்குதல், கசக்கி, அழுத்து அழித்தல், நசுக்குதல், நசுக்குதல், நசுக்குதல், இடித்தல், நசுக்குதல் ஈர்ப்பு நண்பரை விட ஒருவரை நீங்கள் விரும்பும்போது உங்கள் மனதை விட்டு வெளியேற முடியாத ஒரு நபர் ஒரு வார்த்தையில் நீங்கள் விவரிக்க முடியாத ஒரு நபர், ஆனால் பல சொற்கள் நீங்கள் விரும்பும் மற்றும் / அல்லது ஈர்க்கப்பட்ட நபர் நீங்கள் மிகவும் கவர்ச்சிகரமான மற்றும் மிகவும் சிறப்பு வாய்ந்த ஒருவரோடு இருக்க வேண்டும் என்ற எரியும் ஆசை படம் : Image விளக்கம் : ...
Read more

shiv chalisa pdf

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