Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method that's a straightforward method that is a simple method of calculating the age. But, since DAX is the most popular language usedin several calculationsin Power BI, a lot of people are unaware of the option available within Power Query. In this blog , I'll detail how simple it is to calculateAge using Power BI using PowerBI. It is a methodis extremely useful for situations when it is necessary to perform the age calculation. Itcan be done in a previous row-by-row basis.

Calculate Age from a date

This is one of DimCustomer table , which is in the AdventureWorksDW table which functions as the column for birth dates. I've eliminated columns that aren't required to make it easier to read;

To calculate how old you are for each client, you'll need:

  • In Power BI Desktop, Click on Transform Data
  • In Power Query Editor window, ensure to select the first column, namely the Birthdate.
  • Select the Add Column Tab to add Columns. It is found under the "From Date & Time" section. Under Date, choose Age

That's about it. This calculates the calculate any variations to your Birthdate column, and the current date and date.

However, the age that is displayed when you look at the Age column doesn't appear like an age. It's due to the fact that it's a Duration.

Duration

Duration is a specific kind of data used to calculate the duration of a query in Power Query which represents the difference in the two DateTime values. Duration is a combination of four values:

days.hours.minutes.seconds

And that's exactly what you'll find in the above information. From a user's perspective they shouldn't be expected to read information in the same way as those mentioned above. There are methods to find each element of the time. Utilizing the Duration menu, there is a way that you can take the value of seconds, minutes, hours weeks and years from it.

For aid in calculating the age in years for instance it is easy to calculate Total Year:

Notice that the duration was measured in days , and then divided in 365 to calculate the year-long amount.

Rounding

Truthfully, nobody declares an age for their children as 53.813698630136983! They say 53, which is reduced to. It's simple to select the Rounding, then click round down using the Transform tab.

This will give you the years of experience:

It's then possible to purify other columns if like (or there's a chance that you've applied transforms through the Transform tab to avoid the need to create new columns) You can also name this column"Age".

Things to Know

  • Refresh The age calculated in this way will be changed when refreshing your database. Every refresh, the data will match the birthdate to the date and date that the refresh was made. This method is an earlier calculation of age. However, if you wish for the age calculation to be performed dynamically by using DAX This is what I've shown how you can make use of.
  • The reason for using Power Query: Benefits of calculating an age with Power Query is that the calculation is carried out after you refresh the report. The calculation is performed using an algorithm that makes the calculation simple, and there won't be any additional overhead when calculating the age using DAX for a measurement of of runtime.
  • Different scenarios. This isn't intended to be used to calculate the age of a person based on their birth date. This could be used to calculate stock age for products as well as the distinct dates and dates from one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He is a graduate of a BSc from Computer engineering. He have more that 20 years of experience in data analysis as well as the BI and databases fields, as well as programming and development mostly with Microsoft technologies. He has been an Microsoft Data Platform MVP for nine years in a row (from 2011 until the present) for his commitment to Microsoft BI. Reza is a frequent blogger, as well as the co-founder and editor of RADACAD. Reza is co-founder and organizer of Difinity the conference held 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 several books on MS SQL BI and also is writing other books. He was also a regular forum participant on online forums for technical matters, like MSDN and Experts-Exchange as well as moderator for the MSDN SQL Server forums, and is an MCP , the MCSE, as well as an MCITP in BI. He is the founder of the New Zealand Business Intelligence users group. In addition, he's the creator of the incredibly popular books Power BI from Rookie to Rock Star, which is available for free and has more than 170 pages of content. It also contains Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
He is an International speaker on 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 ambition is to help you discover the right solutions for data. He's a Data enthusiast.This post was posted as Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. The following is a great resource for you to bookmark.

Post navigation

Create Different Visual Pages by using Different Security Groups in Power the BIAge's Calculation of Years that is used to calculate Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

digital-converter

Parts per Million by Weight in Water It is the concentration at ppm of gas present in water is usually expressed as weight. To measure this concentration by metric units, an estimate of the density in water is necessary. The density of water that is pure has to be 1000.0000 kilograms per Meter 3. at temperatures of 3.98degC and the normal atmospheric pressure as of 1969. This was the first measurement of kilograms. The kilo of today is defined as the equivalent weight of the prototype used in global use for the kilogram. Water that is high-purity (VSMOW) when temperatures are 4 degC (IPTS-68) or typical atmospheric pressure is an average densities that is 999.9750 kg/m 3. . [5] The density of water is affected by temperature, pressure and impurities, i.e. the dissolved gasses and acidity and saltiness in water. This alarming concentration of gaz which are dissolved into the water could affect how dense the water. There is a possibility that water has a certain concentra...
Read more

convert-to-bmp

convert-to tiff The TIFF format TIFF (also called TIF (Tagged Image File Format) is a Renderer Images type of file standard.Its structure can describe image information in grayscale as in color palettes as well as, in other cases, the colors of different colors. It's possibly it's the most versatile and diverse bitmap format that's currently available. Its flexibility as well as support with a variety of techniques of compression allows developers to customize it to their specific requirements. TIFF format is designed to meet specific storage requirements of the data. For more details about JPEG format, please visit our blog. JPEG format Read our blog to find more information at our ORPALIS website . A brief summary of the history In 1986, the format was developed by Aldus Corporation, the TIFF format was created and developed Aldus Corporation in response to the opinions of scanner manufacturers as well as software developers. The goal of the ...
Read more