Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, as DAX is the primary language usedin numerous calculationsin Power BI, many are not aware of this feature of Power Query. In this article, I'll show the process of how to calculateAge within Power BI using Power. It is a methodis extremely useful when your estimate of the agecan be done as an earlier calculated row-by-row row basis.

Calculate Age from a date

Below is the DimCustomer table, which is a comprised of AdventureWorksDW table, which acts as the birthdate column. I've taken out a few of the columns that aren't needed so that it is easier understand.

To calculate your age for each of your customers the only thing you need to do is to:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; pick the first column for the Birthdate column.
  • go to add Column Tab, and then hit"Add Column Tab," then click on the "From Date & Time" section, and under Date, select Age

That's it. this is how you calculate an amount which is the total of the column for Birthdate column, in addition to the current date and time.

However, the date, which is displayed in the Age column, doesn't appear to be an actual age. It's due to the fact that it's an actual length.

Duration

Duration is a particular form of data that is part of Power Query which represents the variation between the two DateTime values. duration is a combination of four values:

days.hours.minutes.seconds

and that is how you can determine the meaning of the data. For the view of the user it is not their responsibility to read the particulars of this. There are techniques that can get every part in the period. When you select the Duration menu, you will see that you are able to extract the number of minutes and seconds along with days, hours and years from it.

To aid in calculating the age in years like, for instance, you can simply hit the Total Year:

Take note that the duration of the time is calculated in days . Then, it's was divided by the days to calculate the annual amount.

Rounding

It's the truth, no person declares that they are 53.813698630136983! They use the word 53 which is round down. It's easy to choose Rounding and round down on the Transform tab.

This will tell you the age you've reached in your years.

You can then clean the other columns if you'd like (or maybe you've made use of transformations through the Transform tab to avoid having the task of creating new columns) This column can be renamed as follows: Age: column"Age"

Things to Know

  • Refresh The age that is calculated this way will get updated each time you are refreshing your information. and each time will compare the birth date to the date and date at the time of refresh. This method is the pre-calculation of age. If, however, you need the calculation to be performed dynamically by DAX Here's how I described the method you could apply.
  • The motive behind Power Query: Benefits of performing an age calculation with Power Query is that the calculation is carried out at the time of refresh of your report, and using an instrument which makes the calculation quicker and easier, and there is no additional overhead for calculating it with the DAX method to determine the time.
  • Alternative scenarios This isn't used to calculate the age from birthdate. this can be used for inventory-level ages for inventory items and also the difference between 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 on Computer engineering; he is more than 20 years old. experience in the area of data analysis, databases, BI, and development mostly in Microsoft technologies. He is a Microsoft Data Platform MVP for nine consecutive years (from 2011 until now) for his passion of Microsoft BI. Reza is a prolific writer for blogs as well as co-founder and the editor for RADACAD. Reza is also the co-founder as well as co-organizer for 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 has written several books on MS SQL BI and also is working on more books. He also was a regular participant in online forums dealing with technical matters like MSDN and Experts-Exchange and was also the moderator of MSDN SQL Server forums, and holds an MCP and MCSE as well as an MCITP of Business Intelligence. He is the lead in the New Zealand Business Intelligence users group. Additionally, the group is writer of the well-known book Power BI from Rookie to Rock Star, which is completely free with over 700 pages of content as well as Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
This speaker has been 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 passion is to help users to find the ideal data solution. He is an avid Data enthusiast.This piece was written by Reza in 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 posted in Power BI. This entry was posted in Power BI. Bookmark the permalink.

Post navigation

- Share Multiple Visual Pages by using Different Security Groups in Power BIAge's Year Calculation that is used to calculate Leap Year in Power BI through Power Query

Comments

Post a Comment

Popular posts from this blog

random number generator

random --- Generate pseudo-random numbers The source code is Lib/random.py This module implements pseudoThis module implements pseudo random number generators for various distributions. For integers, there is a uniform selection of a range. For sequences there is uniform selection of the random element which is it is a method to generate a random permutation of a set in-place as well as a function that allows random sampling without replacing. On the actual line, there are tools to calculate normal, uniform (Gaussian), positive exponential, lognormal, beta, and gamma. For generating distributions of angles, that of the von Mises distributive is accessible. Nearly all module functions All modulefunctions rely on the basic routine random() , which generates a random float evenly across the semi-open range [0.0, 1.0). Python employs it's Mersenne Twister as the core generator . It creates floats with 53 bits of precision with a length of 2**19937-1. The underlying ...
Read more

scientific calculator

Calculator Use Graphical, scientific and four-function calculators are allowed or offered for certain Practice (r) tests. Unless it is declared that a particular calculator is permitted for a particular test Calculators are not allowed to be employed. Test takers are not permitted to share calculators. Tests That Do Not Permit Use of a Calculator In general, unless specifically stated that a specific kind of calculator is allowed or necessary for a specific exam, calculators can't be used in all Practice test. Tests That Permit the Use of a Calculator You can use a four-function or scientific calculator for your Middle School: Content Knowledge (5146) test, though you're not required to do so. Bring your own calculator to the testing centre. One is not provided on the day of the exam. A graphing calculator isn't allowed during this test. Types of Calculators That Are Not Permitted The following are not permitted for Praxis exams: Portab...
Read more

convert-to-tiff

convert to BMP Do you want to convert Images into another format? Examples include JPG into PNG JPG or vice versa? Maybe you can convert an image to a PDF? It's either for performance or quality , or any other reason There are a lot of situations where you're forced to alter the formatting of an image. In this article we'll present six amazing conversion image programs to help you change images rapidly. image in one format and different formats (or even change images in bulk, if you need to). Six of the best tools for converting image to a new format After we've discussed the reasons for changing the image to an alternative format, we'll look at the best six options. They're all similar in terms of features and choices, so which is most appropriate for you will depend on their amount of images that you'll need to convert often. Convertio CloudConvert PhotoBulk Tinybeest ...
Read more