Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple way of calculating the age. However, since DAX is the preferred languagein several testsin Power BI, many do not know about the functionality available in Power Query. In this blog , I'm going to describe how simple it is to calculateAge in Power BI using Power BI. The methodis exceptionally efficient in cases where the age calculationcan be carried out on an already calculated row-by-row basis.

Calculate Age from a date

That's the DimCustomer table that is part of the AdventureWorksDW table which as a birthdate column. I've removed several of the extra columns in order to make it easier to read;

If you wish to calculate the age of each customer the only thing you need to do is to:

  • In Power BI Desktop, Click on Transform Data
  • In Power Query Editor window; pick the Birthdate column first.
  • 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 equal to the sum of the Birthdate column, as well as the current date and hour.

However, the number you see under the Age column, doesn't appear to be an age. This is because it's a duration.

Duration

Duration is an exclusive data type found in Power Query which represents the distinctions between Two DateTime values. Duration is a mix of four different values:

days.hours.minutes.seconds

And that's how you consider the data above. But from a user's point of view, it's not a good idea to go looking for specifics like this. There are ways you can get each piece of information by examining the length. If you choose the Duration menu it will display that you can find the amount of seconds days, minutes as well as days and years from it.

To apply for the method of calculating the age in years such as you simply click on Total Years:

Note that the length for the course is measured in days . Then, it is divided by 365, which will give you the year-long value.

Rounding

As for the last point, nobody claims that their years of age are 53.813698630136983! They are referring to it as 53 with a rounding down. You can select Rounding as well as Round Down under the Transform tab for it.

This will give you an estimate of your age in years:

After that, you can tidy up the other columns, if you wish (or perhaps you've applied transformations by using the Transform tab to avoid the formation of columns) Also, name this column; Age:

Things to Know

  • Refresh The age determined through this method will be refreshed at the time when your database is refreshing. Every time, the system will compare dates of birth to the date and day on which the data refresh occurred. The method provides an earlier estimation of the age. If you're in need of your calculation to be performed quickly using DAX, here I explained the methods you can use.
  • How to utilize Power Query Benefits of performing age calculations in Power Query can be that this calculation takes place when you refresh your report. This is done making use of an instrument that makes the calculation straightforward, and there won't any extra work to calculate it with DAX because it is a measurement runtime.
  • Other scenarios It is not used to determine age on the basis of the date of birth. This is a good way to determine product inventory and to determine the difference between different dates 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. The engineer has over 20 years' experience in data analysis database programming, BI and development, mainly based using Microsoft technologies. He is an official Microsoft Data Platform MVP for nine consecutive years (from 2011 until the present) for his dedication in Microsoft BI. Reza is an incredibly prolific writer and co-founder of RADACAD. Reza is also co-founder as well as co-chairman of 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 written a few ebooks about MS SQL BI and also is writing a few more. He was also an active member of online forums on technical issues , such as MSDN and Experts Exchange. He was the moderator for MSDN SQL Server forum, and holds the MCP and MCSE certifications as well as an MCITP of BI. He is the director of the New Zealand Business Intelligence users group. He is also the writer of the book that is very well-loved Power BI from Rookie to Rock Star, which is free and contains greater than 700 pages of information as well as 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 in addition to SQL group for users. And He is a Microsoft Certified Trainer.
Reza's goal is to help users find the right 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. This is an excellent reference to bookmark.

Post navigation

- Share Different Visual Pages with different Security Groups Power BI's Age Calculation that can be used to calculate Leap Year within Power BI by using Power Query --

Comments

Post a Comment

Popular posts from this blog

Image to JPG Converter OnlineConvert your images to JPG format with the best image to JPG converter online.

 Do you have images that you need to convert to JPG format? If so, then you'll want to check out our Image to JPG Converter Online. With this converter, you can easily convert images to JPG format with just a few clicks. Plus, you can also preview the images before you download them. So why wait? Try our Image to JPG Converter Online today! If you're looking to convert an image to the JPG format, there are a few ways you can do it online. In this article, we'll show you how to convert an image to JPG using an online converter. To start, select the image or images that you want to convert on the Image to JPG Converter. You can select multiple images by clicking on the "Add Images" button. Once you've selected all the images you want to convert, you'll see a preview of all the selected images on the converter. You can also easily add or remove images from the list by clicking on the "Add Images" or "Remove Images" buttons. If you need to...
Read more

What is ransomware exactly?

Intruders can steal our cash using sophisticated methods as technology improves. It is common to receive short messages in the mail or on our mobile device , containing images or software that could "interest us" however, in fact, are viruses that get control over our devices or computers. For instance, you could put up an advertisement on an online platform for selling a particular product. Within a few hours you get an SMS that offers to swap your product for another and a link to the photos of this product. In actual, if access this link using an Android device, you install an application that transfers all your money to the account of the intruder. Another method of attack used is to send mail messages that include link text as well as a linkthat encourages users to click the link. For instance, an intruder could promise you a lucrative job, but you have to fill out an application by clicking the link. In the process, your computer or mobile device will be inf...
Read more

Scientific Calculator

Calculator Navigate to: previous, Jump to (Calcium)Next (Caleb)A fundamental calculator It is a calculator it is the device used to perform calculations . Modern calculators typically incorporate general-purpose computing technology, but they're often made for specific purposes. For instance graphing calculators are focused on graph-centered mathematics, such as trigonometry and statistical. Furthermore modern calculators are far more mobile than most computers although some personal digital assistants (PDAs) are similar in size and dimension as hand-held calculators. As calculators have the capacity of being tiny but in the future devices that are smaller than credit cards will be able to perform exactly the same calculations that are performed in larger scientific calculators. Another option for development is like a handheld computer that has notations entered by pressing buttons, but instead are displayed on a screen. This means that the need for buttons woul...
Read more