Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, because DAX is the most popular language usedin numerous calculationsin Power BI, a lot of people do not have a clue about the feature available in Power Query. In this blog article I'll go over how simple to calculateAge in Power BI and Power BI. The methodis extremely useful in situations where you need to calculate your age calculationcan be performed in a pre-calculated row row basis.

Calculate Age from a date

Below is the DimCustomer table that is part of the AdventureWorksDW table that acts as the "birthdate" column. I've eliminated columns that don't have any value so that it is easier to study.

For you to calculate your age for each person the only thing you'll need is:

  • In Power BI Desktop, Click on Transform Data
  • On the Power Query Editor window; start by selecting the column titled Birthdate.
  • Click on the Add Column Tab, and follow"Add Column Tab," then click on the "From Date & Time" section. In the Date, choose the age range.

It's that simple. this does calculate any differences within the Birthdate column, as well as the current date and time.

But, the age that shows up in the Age column, doesn't really look like an age. This is because it's the term "Duration.

Duration

Duration is a distinct kind of data types used as a the data type used in Power Query which is used to represent the differences between the Two DateTime values. Duration is a mixture of four numbers:

days.hours.minutes.seconds

This is how you understand the above numbers. But from a user's perspectives, they shouldn't be expected to research information like that. There are ways you can obtain each of the segments that are the amount of time. If you click on the Duration menu, you'll discover that you can get the number of seconds to minutes, hours days, and years.

For calculating the age in years for instance, it's simple to calculate Total Year:

The duration is measured in terms of days, then that divided by the number of days to provide the annual amount.

Rounding

There is no truth to it, and nobody claims that their age is 53.813698630136983! The people call it 53 and then they round it down. You can choose Rounding after which you can select Round Down on the Transform tab for it.

This will give you the age in years:

Clear the other columns If you wish (or this could mean that you made use of transformations by using the Transform tab to not create new columns), and call this column"Age:

Things to Know

  • Refresh The date calculated in this way will get refreshed every time you're refreshing your data. Each time, the system will evaluate dates of birth along with the dates and times for refreshing. This method is the initial calculation of age. If you want the calculation to be dynamically done using DAX This is how I've explained the best way to use.
  • Why Power Query: Benefits of making age calculations with Power Query is that the calculation takes place at the time when your report is refreshed. It refreshes the report using the Power Query tool, which makes calculation simpler, and there's no cost associated with formulating it using DAX for a measure time.
  • Another reason is that it's not meant to calculate age only by birthdate. This is a way to calculate the quantity of inventory for products as well as the differences between two dates or times from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He is a BSc with a major in Computer engineering. He holds an impressive 20+ years' experience in data analysis and BI, database programming and development primarily based with Microsoft technologies. He was an official Microsoft Data Platform MVP for nine consecutive years (from 2011 until now) because of his dedication for Microsoft BI. Reza is a prolific blogger, as well as the co-founder and editor of RADACAD. Reza is co-founder as well as co-organizer for the Difinity event located at 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 working on other books. He was also a frequent participant in online forums for technical questions like MSDN and Experts-Exchange as well as moderator of MSDN SQL Server forums, and is an MCP and MCS as well as an MSCITP of the BI. He is the head of the New Zealand Business Intelligence users group. He is also the editor of the book that is extremely well-loved Power BI from Rookie to Rock Star it is free and contains nearly 1700 pages of content and a companion book called Power BI Pro Architecture published by Apress.
His credentials includes being 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 goal is to help users find the right data solution. He is a Data enthusiast.This article was posted as Power BI, Power BI from Rookie to Rockstar, Power Query and is filed within Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

- Share different visual pages with different Security Groups Power BIAge's Year Calculation, which works for Leap Year in Power BI using Power Query

Comments

Post a Comment

Popular posts from this blog

angle-converter

 <h2>  <a href="https://aboneapp.com/#/angle-converter">What's the definition for the word "angle"</a>? A angle which is acute, narrow, or narrow. </h2> <p>  An angle is a <strong>shape formed by two rays which have a common starting point known by the <em>vertex</em></strong>. It is a matter of what are the advantages of angles? It is possible to calculate the height of a tower when you know how far from you and angles between its surface and the top. Utilizing the same <a href="http://%2A%20https//en.wikipedia.org/wiki/Technique">technique</a> employing the same technique it is possible to determine the size of the moon's diameter or with the right instruments and equipment, the size of our own planet. If you throw an object and want to determine the distance it travels you'll have to know the angles at which it is thrown. There are many applications where angles are useful how...
Read more

What is an Influencer?

  influencer meaning in tamil Influencer Meaning in Tamil  | More Tamil Words for Influencer Nearby Translations influenzas influenzic influx influxable influxes What is an Influencer ?   Social Media Influencers Defined (Updated 2021) Written by  Werner Geyser. Last update:  June 14th 202112 minutes to read Marketing Influencers You can find additional posts on Influencer Marketing by searching this site.   Here's our guide to Influencer Marketing: A Beginner's How-to.   Before you take on influencer marketing, you must first ask yourself a basic inquiry you must ask. What is it to be an influencer? Influencer  Someone who has: The ability to influence purchasing decision of others through his or her authority or knowledge, position or relationship with the people who are his or her audience. A follower is collection of individuals who are active within a specific area.   The niche in which the person is focusing can determine how big their ...
Read more

illuminance-converter

Image
What is the definition of an angle ? An angle that is acute, narrow, or narrow. An angle is a form formed by two rays which have the same starting point which is known as the vertex. Vertex. It is possible to ask: What are the advantages of angles? It is possible to figure out the height of a tower when you know the distance from you it is and the angle that it has the top of the tower and the ground. With this method, you can find out the size of the moon is, or, if you have the appropriate tools, the entire circumference of the globe. If you throw something and you'd like to know the distance it goes, you need to determine the angle the object is being thrown at. There are many other situations that could benefit from angles however at this point we'll concentrate on the basics of geometry. Angles are classified in terms of their dimensions: WHAT IS A FREQUENCY CONVERTER ? A frequency converter is often called a power frequency converter . It is a device that absor...
Read more