Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, since DAX is the main language usedin numerous calculationsin Power BI, many aren't aware of this feature in Power Query. In this article, I'll describe the process of how to calculateAge using Power BI with Power BI. It is a methodis extremely useful when your estimation of your agecan be calculated on an earlier calculated row by row basis.

Calculate Age from a date

Below you will find the DimCustomer table which is one of AdventureWorksDW table that acts as"the birth date" column. I've removed a few additional columns for easier understand.

In order to calculate your age for each of your customers all you have to do is to:

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

That's that. this is how you calculate an amount that is the total of the Birthdate column, in addition to the date and time of the present.

But, the age that appears under"under the Age column, and doesn't seem to be an actual age. It's because it's an actual length.

Duration

Duration is a particular kind of data within Power Query which represents the difference of two DateTime values. Duration is the combination of four numbers:

days.hours.minutes.seconds

This is the way to determine the meaning of the data. But, from the point of view of the user it is not their responsibility to read the specifics of this. There are ways to extract every bit of information from the period. If you choose the duration menu you will be able to calculate the amount of minutes and seconds, hours, days and years out of it.

To help in calculating the age in years for example it is easy to hit Total Year:

It is important to note that the duration is measured in days . Then it is subtracted by the amount of days to give the total annual amount.

Rounding

In reality, no person claims to be 53.813698630136983! They claim 53, which is an arbitrary number that is rounded down. It's simple to choose Rounding and Round Down from the Transform tab.

This will let you know that you're old enough to be

It is then possible to clean other columns, if desired (or maybe you've taken advantage of transformations using the Transform tab to not have to create new columns) And name this column"Age:

Things to Know

  • Refresh The age that is calculated this way is updated every time you are refreshing your information. Each time, it will compare the birthdate to the date and date at the time of refresh. This method is an algorithm for pre-calculating an age. If you require the calculation be performed dynamically using DAX, here's the method I used to describe the method you can apply.
  • The reason for Power Query: Benefits of doing an age calculation with Power Query is that the calculation is carried out while you refresh your report. This is done using an instrument that makes the calculation more efficient and speedier, and there's no added cost for calculating it with DAX to measure the time.
  • Alternative scenarios It cannot be utilized to calculate the age from birthdate. This is a good way to determine inventory-level ages for inventory items and the differences between two dates and times from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds the BSc in Computer engineering. He has more than 20 years old. experience in the area of data analysis, databases, BI, and development mostly in Microsoft technologies. He is an official Microsoft Data Platform MVP for nine consecutive years (from 2011 until today) because of his love of Microsoft BI. Reza is an avid blogger as well as co-founder and editor of RADACAD. Reza is also the co-founder and co-organizer of Difinity Conference. Difinity Conference located 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 wrote several books on MS SQL BI and also is working on more books. He was also a regular participant in online forums dealing with technical issues such as MSDN and Experts-Exchange and was moderator of the MSDN SQL Server forums, and is an MCP and MCSE and an MCITP of Business Intelligence. He is the leader in the New Zealand Business Intelligence users group. Also, he's the creator of the highly acclaimed publication Power BI from Rookie to Rock Star, which is free with more than 170 pages of content. The Power BI Pro Architecture published by Apress.
He is an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, and SQL users groups. And He is a Microsoft Certified Trainer.
Reza's love is helping users find the most effective data solution. He is a Data enthusiast.This report was written by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is categorized within Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. You can follow any responses to this entry through the RSS feed.

Post navigation

- Share different visual pages using different security groups in Power BIAge in Years Calculation that can be used for Leap Year in Power BI through Power Query

Comments

Post a Comment

Popular posts from this blog

Durga Chalisa Lyrics in Hindi

  Durga Chalisa Lyrics in Hindi ( durga chalisa pdf ) नमो नमो दुर्गे सुख करनी । नमो नमो दुर्गे दुःख हरनी॥ निरंकार है ज्योति तुम्हारी। तिहूं लोक फैली उजियारी॥ शशि ललाट मुख महाविशाला। नेत्र लाल भृकुटि विकराला॥ रूप मातु को अधिक सुहावे। दरश करत जन अति सुख पावे॥ तुम संसार शक्ति लै कीना। पालन हेतु अन्न धन दीना॥ अन्नपूर्णा हुई जग पाला। तुम ही आदि सुन्दरी बाला॥ प्रलयकाल सब नाशन हारी। तुम गौरी शिवशंकर प्यारी॥ शिव योगी तुम्हरे गुण गावें। ब्रह्मा विष्णु तुम्हें नित ध्यावें॥ रूप सरस्वती को तुम धारा। दे सुबुद्धि ऋषि मुनिन उबारा॥ धरयो रूप नरसिंह को अम्बा। परगट भई फाड़कर खम्बा॥ रक्षा करि प्रह्लाद बचायो। हिरण्याक्ष को स्वर्ग पठायो॥ लक्ष्मी रूप धरो जग माहीं। श्री नारायण अंग समाहीं॥ क्षीरसिन्धु में करत विलासा। दयासिन्धु दीजै मन आसा॥ हिंगलाज में तुम्हीं भवानी। महिमा अमित न जात बखानी॥ मातंगी अरु धूमावति माता। भुवनेश्वरी बगला सुख दाता॥ श्री भैरव तारा जग तारिणी। छिन्न भाल भव दुःख निवारिणी॥ केहरि वाहन सोह भवानी। लांगुर वीर चलत अगवानी॥ कर में खप्पर ...
Read more

KGF Full Form The complete form of the KGF?

  KGF Full Form  The entire form of the KGF? It is the mining zone of Bangarpet Taluk, Kolar District situated in Karnataka.   It is the largest Indian mine for gold.   It was shut down in 2001 because of the increase in costs for production and production, as well as fewer gold deposits. It was the second deepest mine site in the world. It was famous for its early extraction of gold.   It's also known as "Little England" because of the stunning views it offers and the dry climate.   It is the location of English roads and bungalows which are well-designed.   Kolar dates back to in around the year 2100 AD and is much older than Bangalore.   Ganga established Kolar as the capital city of her city, and also served as Governor of Mysore and Coimbatore. The story of Kolar Gold Fields|  full form of kgf K  The olar Gold Fields  (KGF) Also known as "Little Britain" is located in the South Indian Kolar District, Karnataka.   It is sit...
Read more

rip meaning in hindi

  How do you define an infraction?   rip meaning in hindi A rip-off can be defined as the event or incident in which the customer been in a position to be charged excessively.   The buyer also regrets  payin that price. the amount.   If I purchase something only to then later refer to it as an illegal ripoff, I'm declaring that I've overpaid on the transaction.   It could also mean I didn't get the results I was expecting.   It could be a reference in the context of fraud or swindle or scam, however it's typically less serious than the other three words.   It could refer to an imitation that is better than the one it's such as an imitation or an imitation. The word "ripoff" is possible for you to compose it with just one or two words, then the addition of a punctuation mark.   It is also possible to write " Rip-off' or a rip-off.   You can make it  possible  however it is less often writing the term in two parts, with no punctu...
Read more