Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, since DAX is by far the most widely used language usedin several functionsin Power BI, a lot of users don't recognize the feature available in Power Query. In this article I will discuss how simple it is to calculateAge within Power BI in conjunction with PowerBI. This methodis extremely helpful for situations when the computation of an agecan be done on a pre-calculated row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table that's one of AdventureWorksDW table which includes an old column. I've eliminated a few additional columns to make it simpler to comprehend.

To calculate an age range for every client, you need is:

  • In Power BI Desktop, Click on Transform Data
  • On the Power Query Editor window; start by selecting the Birthdate column.
  • Click on the Add Column Tab, which is under the "From Date & Time" section. Under Date select the age range.

This is all there is to it. This can calculate an amount that's the product of the column for birthdate, Birthdate column and the current date as well as time.

However, the appearance of the age in this Age column, does not seem to look like an actual age. It is due to the fact that it is a time period.

Duration

Duration is a particular kind of data format that is found in Power Query which represents the distinction between two DateTime values. Duration is a mix with four values:

days.hours.minutes.seconds

This is what you will see in the above values. From a personal viewpoint, they shouldn't have to research specifics that are similar to the ones above. There are ways to be used to determine every minute of the time. With the Duration menu choice, you'll see the number of seconds, minutes, hours, and years out of it.

To calculating the age in years like, for instance it is as simple as going to Total Years.

The duration is measured in days and then divided by 365 to calculate an annual number.

Rounding

No one has claimed that they're 53.813698630136983! they claim 53 with the rounding down. You can simply select the Rounding option . You can then round down the Transform tab.

This will show you what your age is in terms of years

It's also possible to tidy other columns, in the event that you want to (or you could have applied transformations within the Transform tab to avoid creating new columns.) The column could be named column: Age.

Things to Know

  • Refresh The age determined by the method can be refreshed every time you are refreshing your dataset. Each time, it'll compare your birthdate with the date and timing at the time of refresh. This method is an algorithm for pre-calculating your age. If you, however, want the age calculation to be performed dynamically, with DAX, this is how I have described a technique you can use.
  • Benefits of using Power Query: Benefits of calculating age in Power Query is that the calculation is done when you refresh your report. This is done by making use of a tool to make the calculations easier, and there's no need to add the cost of doing it using DAX as a measure of the runtime.
  • Another possibility is that it cannot be used to calculate the age by birthdate. It could be used to determine inventory of products as or the difference in two 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 holds a BSc in Computer engineering. Over 20 years' experience in data analysis database programming, BI, development and programming primarily focused specifically on Microsoft technologies. He is an official Microsoft Data Platform MVP for nine years (from 2011 to the present) due to his devotion in Microsoft BI. Reza can be described as a prolific writer and co-founder of RADACAD. Reza is also co-founder as well as co-organizer for 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 wrote a couple of books about MS SQL BI and also is writing a few more. He was also a regular member of online forums for technical issues like MSDN and Experts-Exchange as well as moderator of MSDN SQL Server forums, and is an MCP , the MCSE, as well as the MCITP for BI. Director of the New Zealand Business Intelligence users group. He is also the author of the very well-known workbook Power BI from Rookie to Rock Star, which is available for free and has more that 17000 pages of data and another book titled Power BI Pro Architecture published by Apress.
It is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, along with SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help you find the most efficient solutions for data, and is an avid Data enthusiast.This article was published in Power BI, Power BI from Rookie to Rockstar, Power Query and related to Power BI, Power BI from Rookie to Rock Star, Power Query. The following article is a good reference to bookmark.

Post navigation

- Share different visual pages using different security groups in Power BIAge's Year Calculation that works for Leap Year in Power BI using Power Query

Comments

Post a Comment

Popular posts from this blog

LENGTH CONVERTER

  LENGTH CONVERTER Choose the length you'd like to use, and then enter the amount you'd like to convert to a different measurement of length in the "From" field, and choose the measurement you'd like to convert it into within the "To" field.   The result of the conversion will be visible within the "To" field right after you click on any unit. It is the term"length," which you've likely heard of , is defined in the context of "length" as a precise  measurement  of anything from one end to the next.   It's difficult to locate exact measurements that convert miles into kilometers.   Sometimes, we require an estimate of the time it will take others to get there or the amount of time it takes to reach there, and that's the reason length converters are useful in calculations.   Measurement  Converter is a tool that can be used according to the requirements since it is able to convert any length, and will assist you in...
Read more

roadies-audition-form-pdf

Roadies Audition Form PDF | Roadies 2021 Audition Dates and Venues Use the link below in order to obtain the Roadies Audition Form PDF . Roadies Audition Form You can sign up for MTV Roadies Revolution 2020 by visiting the official website or the show's website. You can sign up by clicking on the button below. You will find the URL to our registration page in this web page. The Roadies Audition Form can be downloaded on the internet in PDF format by clicking the link below. Date and Venue MTV India Roadies Audition 2021 Rev X9 Registration Online for Season 19 Auditions Date, Venue, Eligibility Criteria and Documents Needed, Terms and Conditions, Selection Process Some television Shows accept auditions because of being affected by the Covid 19 Pandemic. The majority of TV Auditions take place in Mumbai and Mumbai, as we are aware. CIDIV Second Wave is coming to Maharashtra , and there are plenty of instances. MTV will conduct the Roadi...
Read more

convert-to-jpeg

convert to BMP How to Create JPG to BMP for Free JPG formats are widespread among the population as phones view pictures in JPG format but BMP format isn't as common among the users as it is used for windows computers. JPG in addition to BMP each formats are suitable to save files onto your computer but you will notice that there are some differences between them. Usually, JPG files have a small dimensions, but they're of poor quality, while if you opt for BMP format for storing your images then the quality of images saved will be higher quality. If you're experiencing issues with low-quality with your images, you can convert JPG files to BMP by making use of desktop and online tools. Here we will show you 5 suggested JPEG in Bitmap converters without difficulty in this post. If you're in search of a the JPG-to BMP converter that is online , online UniConverter (Originally Media.io) ought to be on your list of options NO.1. You'll need to have the que...
Read more