Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method that is a quick and easy method in calculating the age. However, since DAX is the most widely used language usedin several calculationsin Power BI, a lot of people do not know about the function available in Power Query. In this article, I'll discuss how easy it is to calculateAge using Power BI with PowerBI. This methodis very beneficial in situations where it is required for the age calculation. Itcan be calculated using an earlier calculated row-by-row basis.

Calculate Age from a date

This is the DimCustomer table . It comes an AdventureWorksDW table that acts as the birthdate column. I've removed the columns that don't need to be there to make it more readable;

To calculate how old you are for every buyer, the following information is required:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window, ensure to choose the Birthdate column first.
  • You can go to the add Column Tab, which is in the "From Date & Time" section. And under Date, choose Age

It's that simple. it. This is how you calculate any variation to the Birthdate column, as well as the current date and time.

The age appears when you look into the Age column, isn't looking like an actual age. It's due to the fact that it's a Duration.

Duration

Duration is a unique type of data that is used by Power Query which represents the differences between the two DateTime values. Duration is a mix of four different values:

days.hours.minutes.seconds

That's what you'll see in the information above. From a user's view, they shouldn't need to study information in the same way as those mentioned above. There are many ways to locate every component of time. If you select the Duration menu you'll be able to see how you can get the number of minutes, seconds, hours, months and days from it.

To aid in calculating the age in years for instance , it's easy to find the Total Year:

It is important to note that the duration is determined in days. It is then divided into 365 days to calculate the year-long amount.

Rounding

Truthfully, no one states that their kid's age is 53.813698630136983! They use 53 which is rounding down. It's easy to select the Rounding option , and then to round down in the Transform tab.

This will give you the year-old number:

It's also possible to cleanse other columns if you'd like (or there is a chance that you've applied transforms through the Transform tab in order to not create new columns) And then name this column"Age"

Things to Know

  • Refresh The age calculated in this manner will be altered at the time of refreshing your database. Every subsequent time, it'll match the birthdate with the date and time that the refresh was made. This method is an earlier calculation of age. If you want the age calculation to be carried out dynamically using DAX This is exactly what I've described how you can make use of.
  • The reasoning behind Power Query: Benefits of calculating an age with Power Query is that the calculation happens every time you refresh your report. It's done with an algorithm which makes the calculation easy, and there will not be extra overhead in calculating the age using DAX for a measurement of of runtime.
  • Different scenarios. This isn't intended to be used to calculate age by birthdate. This could be used for stock age for products as well as the various dates and dates that differ from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has an BSc at the University of Computer engineering. He have more that 20 years of working in the field of data analysis and database programming, BI and development generally making use of Microsoft technologies. He was a Microsoft Data Platform MVP for nine years in a row (from 2011 until now) because of his commitment to Microsoft BI. Reza is a regular blog writer, and the co-founder and editor of RADACAD. Reza is also co-founder and organizer of Difinity the conference that is held 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 co-authored several books on MS SQL BI and also is writing other books. He also was a regular participant in online forums on technical issues like MSDN and Experts-Exchange, and was moderator of MSDN SQL Server forums, and is an MCP as well as the MCSE, as well as an MCITP in BI. He was the creator of the New Zealand Business Intelligence users group. Additionally, he's an author of the extremely popular workbook Power BI from Rookie to Rock Star, which is freely available and contains more than 17000 pages of information and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
This speaker has been an International speaker in 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 desire is to assist you find the perfect solution for data, and he's a Data enthusiast.This entry was posted under 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 good source to bookmark.

Post navigation

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

Comments

Post a Comment

Popular posts from this blog

Random Number Generator

Random Number Generator Utilize the generator to get an absolutely randomly digitally secure number. It generates random numbers that can be employed when reliability of results is critical in shuffles of deck of cards to play an online game of Poker or drawing numbers for sweepstakes, giveaways or lottery. What's the best way to select the best random number between two numbers? It is possible to use this random number generator for you to generate a reliable random number from any two numbers. To obtain, for example, a random number between one to 10 which includes 10, you have to type in 1 first into the input box and 10, in the second input field, then press "Get Random Number". Our randomizer will select one of the numbers from 1 to 10 random. To create an random number between 1 and 100, use the same procedure with 100, however, it's in the 2nd field of the randomizer. To creating the illusion of rolling dice the number range should be 1-6 f...
Read more

compress-image

convert to jpeg How to Convert Images From PNG to JPG and JPG to PNG When I started my career in marketing and needed to incorporate images into my writing or post a photo on Facebook I didn't really care about the style of the image . It wasn't a concern to me. For I, JPG and PNG were identical. I was beginning to realize the distinctions between them, but realized that they're neither identical. However, neither is superior to the other. JPG is perfect for specific circumstances, while PNG is the best option for others. In the end, JPG images are best utilized for social media as well as blog content due to their design for photography. Furthermore, they have smaller file sizes than pictures in PNG formats. PNG images are excellent for professional photography and web graphics as they maintain their quality regardless of how often you save them and then access them, whereas JPG images get less quality every time you save and open them. Fortunate...
Read more