Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way for calculating the age. However, because DAX is the preferred languagein various researchesin Power BI, many are unaware of the function available in Power Query. In this post, I'm going to describe how simple it is to calculateAge in Power BI through Power BI. This methodis highly effective when Age calculationcan be performed on a previously calculated row-by - row basis.

Calculate Age from a date

It's the DimCustomer table that comes from the AdventureWorksDW table, which has the birthdate column. I've removed a few of the columns that aren't needed in order to make it easier to read;

If you want to calculate the amount of time each customer has been in the market, all you have do is:

  • In Power BI Desktop, Click on Transform Data
  • In Power Query Editor window, select the first column, Birthdate.
  • Click on the Add Column Tab. Under "From Date & Time" section, and under Date Choose the appropriate age range.

This is it. This will calculate an amount that is the total of the Birthdate column, along with the current date and time.

However, the number displayed under the Age column, doesn't appear to be an actual age. It's due to the fact that it's a length.

Duration

Duration is a distinctive data type on Power Query which represents the variations between Two DateTime values. Duration is a mixture of four different values:

days.hours.minutes.seconds

and that's the way to take the information above into consideration. From the perspective of users, you don't want them find specifics such as this. There are ways to extract each element by examining the length. Selecting the Duration menu it will reveal that you can find the amount of seconds days, minutes days, years, and seconds from it.

To apply an method that involves calculating the age in years like, for example you simply choose Total Years.

Make note of the fact that the duration that the programme runs is calculated in days . Then, it is divided by 365 days to give you the yearly value.

Rounding

The final thing to note is that nobody says their date of birth is 53.813698630136983! They refer to it as 53 with a rounding down. You can select Rounding and Round Down by clicking on the Transform tab for it.

This will give you the age in years:

After that, you can tidy up the other columns, if desired (or perhaps you've applied transformations through the Transform tab to stop the formation of columns) And name this column: Age:

Things to Know

  • Refresh The amount of time calculated using the method can be updated when the time comes when your database is refreshing. And each time it will compare the date of birth with the date and date on which the data refresh occurred. This method will provide an older calculation for age. If you need that calculation be completed by DAX in a way that is dynamically performed DAX Here I have explained how to use it.
  • How to use Power Query: Benefits of making age calculations with Power Query is that the calculation occurs during the refresh of your report. This is achieved by using an instrument which makes the calculation simple, and it won't any extra work to calculate the calculation using DAX because it is a measurement runtime.
  • Additional scenarios for calculating age only by the date of birth. This is a good way to determine product inventory and to distinguish different dates from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc from Computer engineering. He has more than 20 years of working experience in data analysis database programming, BI and development primarily based on Microsoft technologies. He has been a certified Microsoft Data Platform MVP for nine years (from 2011 until now) for his commitment to Microsoft BI. Reza is an incredibly prolific writer and co-founder of RADACAD. Reza is also co-founder and co-organizer of the Difinity conference 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 also written a few ebooks in the field of MS SQL BI and also is working on several additional. He was also a regular participant in forums online for technical matters like MSDN and Experts-Exchange . He also served as the moderator of MSDN SQL Server forum which is where he holds the MCP, MCSE as well as an MCITP in BI. He is also the head of the New Zealand Business Intelligence users group. Also, he is the author of the loved by many. Power BI from Rookie to Rock Star, which is free and has over 700 pages of content in addition to The Power BI Pro Architecture published by Apress.
The speaker is an International speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday along with SQL Users Groups. And He is a Microsoft Certified Trainer.
Reza's dream is to help users find the best data solution. He is an avid Data enthusiast.This report was published as part of 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 guide to save.

Post navigation

Share Different Visual Pages with Different Security Groups in Power BIAge's Age Calculation which is used to calculate the Leap Year in Power BI by using Power Query --

Comments

Post a Comment

Popular posts from this blog

The Best Temporary Email Services to Use in 2022

Image
 A temporary email address is just the thing you need for those times when you're hesitant to use your real email address. Whether you don't trust a platform, you only use your email address sparingly, or you want to access your email anonymously , a disposable email address can keep your mailbox organized and spam-free. A temporary email, also known as a throwaway email, is a self-destructing address that can help you escape spam and stay anonymous online. Temp emails are best used for sending and receiving non-confidential messages. to sign up for websites you don't trust or rarely use. That way, you can avoid spaThere are a lot of reasons why you might want to use a temporary or disposable email address. Maybe you don't trust a platform, you only use it sparingly, or you want to access it anonymously. Whatever the reason, a disposable email address can keep your mailbox organized and spam-free.m and other online threats.
Read more

Calorie Calculator

What is Calorie Counting? Calories are important , but they're not mandatory for every person! What Is Calorie Counting? Calorie counting is the method of recording the calories (or Kilojoules) quantity of each meal and drink that consumed every day. It is possible to do this using the kitchen journals application or simply entering your data into a spreadsheet. Alternatively, meals made based on calories may be planned ahead and adhered to precisely. Do Calories Count? There are two opinions regarding this topic: Calories or calories can be the only thing to consider and must be counted. Calories aren't the only thing that matter. It's macronutrients (i.e. carbohydrates, protein, and fats) that are crucial to everything. Concerning energy consumption and output calories, they are everything. In terms of nutrition and health, the macronutrients count. Imagine that you decide to follow an 1800 calorie a day diet - do you want to dri...
Read more

convert-to-png

convert-to tiff The TIFF format TIFF, also called TIF (Tagged Image File Format) is a format for raster Images formats standard.Its structure can be used to describe images data that are in grayscale as well as color palettes and, in various other cases colors in different color spaces. It's possibly the most flexible and diverse bitmap formats that exist. Its extensibility as well as support for diverse data compression techniques allow developers to customise it to suit their needs. TIFF format to meet your specific requirements in data storage. For more information about JPEG format, see our blog post at the ORPALIS website . A brief overview of the history It was first developed in 1986 through Aldus Corporation, the TIFF format was developed in response to feedback from scanner manufacturers as well as software developers. Its principal goal was to provide a standard for rasterized files of scanned images for scanners of all manufacturers. In t...
Read more