Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method that's a basic method for calculating the age. However, because DAX is the most well-known language usedin several calculationsin Power BI, many are unaware of the function available in Power Query. In this blog post, I'll describe how simple the process is calculateAge with Power BI using PowerBI. It is a methodis very useful when it is necessary to perform the age calculationcan be done on an earlier calculated row-by-row basis.

Calculate Age from a date

It's what's known as the DimCustomer table , which comes an AdventureWorksDW table that functions in the capacity of the date-of-birth column. I've removed the columns that don't need to be there to make it easier to read;

For you to calculate your age range for every buyer, you need is:

  • In Power BI Desktop, Click on Transform Data
  • In the PowerQuery Editor window, ensure that you select the column titled Birthdate first.
  • To add columns, click on the Column Tab and click on the Add Column Tab, which is under the "From Date & Time" section. Then, under Date, select Age

This is all there is to it. This will calculate any differences in both the Birthdate column, and the current date and time.

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

Duration

Duration is a special type of data that is utilized within Power Query which represents the differences between two DateTime values. Duration is a combination of four different values:

days.hours.minutes.seconds

This is what you'll see in the information above. But from a user's perspective they shouldn't be expected to read information in the same way as those mentioned above. There are ways to get each of the components of the time. By using the Duration menu, there is a way that you can get the number of minutes, seconds, hours, days , and even years from it.

To assist with calculating the age in years for instance it is easy to find the total Year:

Notice that the duration was measured in days , and then divided into 365 days to give the annual amount.

Rounding

It's the truth, no one identifies the age of their child as 53.813698630136983! They use 53 which is an rounded down. It's very easy to select Rounding and then round down by selecting the Transform tab.

This will give you the number of years in years:

It's also possible to tidy other columns as you'd like (or it is possible that you've applied transforms on the Transform tab to prevent the creation of new columns) And then name this column: Age.

Things to Know

  • Refresh The age calculated this way will get changed when refreshing your database. Each subsequent time, it'll match the birthdate with the date on which the refresh of the database took place. This method is an earlier calculation of age. However, if you'd like your age calculation to be conducted dynamically by using DAX This is what I've described the best way to use.
  • The motive behind Power Query: Benefits of calculating an age with Power Query is that the calculation is performed after you refresh the report. It is done using an algorithm which makes the calculation simple, and there won't an additional cost in calculating the age using DAX in order to determine runtime.
  • Different scenarios. This isn't designed to calculate the age of a person based on their birth date. This can be used to calculate product inventory age and the date and time that differ from each other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds earned a BSc in Computer engineering. He has more than 20 years of experience in data analysis and programming, databases, BI and development mostly utilising Microsoft technologies. He has been a Microsoft Data Platform MVP for nine consecutive years (from 2011 until now) because of his commitment to Microsoft BI. Reza is a regular blog author, and is the Editor and Co-founder for RADACAD. Reza is also co-founder as well as coordinator 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 wrote a variety of books on MS SQL BI and also is writing other books. He was also a regular member of online forums on technical issues like MSDN and Experts-Exchange and was moderator on the MSDN SQL Server forums, and holds an MCP and MSSE as well as an MCITP in Business Intelligence. He was the creator of the New Zealand Business Intelligence users group. He's also the creator of the incredibly popular workbook Power BI from Rookie to Rock Star, which is available for download for free and includes more that 17000 pages of information and the Power BI Pro Architecture published by Apress.
This speaker has been an International speaker for 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 aim is to help you find the ideal solutions for data, and he's an avid Data enthusiast.This article was uploaded within Power BI, Power BI from Rookie to Rockstar, Power Query and included in Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic resource to bookmark.

Post navigation

Share various visual pages using different security groups within Power BIAge's Calculation of Years that is used to calculate Leap Year in Power BI by making use of Power Query

Comments

Post a Comment

Popular posts from this blog

Random Number Generator

Random Number Generator Make use of this generator to create a trully random and cryptographically safe number. It generates random numbers that can be employed when accuracy of the results is essential in shuffles of cards for an online poker game, or when drawing numbers for raffles, lottery or sweepstakes. How do I pick an random number from two numbers? Use this random number generator to select a true random number from any two numbers. To obtain, for example the random number from 1-10 with 10, you must enter 1 first in the box followed by 10 in the second and click "Get Random Number". The randomizer will select the number 1-10 at random. For generating an random number between 1 and 100, follow the same procedure however, with 100 within two fields on the selector. When you wish to simulate a dice roll, the number should range from 1 , for a typical six-sided dash. If you'd like to generate an array of unique numbers, select the you'll...
Read more

digital-converter

Parts per Million by Weight in Water It is the concentration of gas at ppm present in water is usually expressed as weight. To determine this concentration using metric units, it is necessary to determine the water's density. is required. The density of water that is pure should be 1000.0000 kilograms per meters 3. at temperatures of 3.98degC and the standard atmospheric pressure as of 1969. This was the original classification of the kilogram. The current definition of the kilo is as equivalent to the mass of the prototype to allow for worldwide use for the kilogram. High-purity water (VSMOW) with temperatures as low as 4 degrees Celsius (IPTS-68) as well as regular atmospheric pressure has at an average density of 999.9750 kg/m 3. . [5] The density of water is affected by pressure, temperature and impurities, i.e. the gasses that are dissolved as well as the salinity of the water. A shocking concentration of the gases that are dissolved in water could affect how d...
Read more

convert-to-bmp

convert-to tiff The TIFF format TIFF (also called TIF (Tagged Image File Format) is an Renderer image File Format standard.Its structure allows it to describe image information in grayscale or grayscale as and color palettes or, in other situations, the color of various colors. It's possibly one of the more flexible and diverse bitmap format in use today. Its flexibility , as well as support in different data compression methods permit developers to tailor it to suit their needs. TIFF format is able to meet the particular storage requirements of data. For more details about how to use the JPEG format Please read our blog for more information on our ORPALIS website . A brief overview of the past In 1986 the format was developed by Aldus Corporation, the TIFF format was designed by Aldus Corporation in response to the feedback of scanner manufacturers and software developers. The primary goal was to develop a common format for raster file containing scan...
Read more