Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. But, as DAX is the most widely used language usedin several calculationsin the Power BI platform, a lot of people don't have any idea about this function in Power Query. In this blog post I will demonstrate how easy to calculateAge in Power BI using Power BI. This methodis very beneficial when the computation of an agecan be conducted in a row-by-row basis.

Calculate Age from a date

The table is called the DimCustomer table that is part of the AdventureWorksDW table which as an age column. I've taken out some columns that aren't necessary in order to make it easier to read;

To calculate the exact age every purchaser, the following information is required:

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

This is all there is to it. This will calculate the amount which is the sum that is the sum of Birthdate column, along with the current date and time.

But, the age that is displayed in the Age column, doesn't look like an age. That is because it is an actual length.

Duration

Duration is a particular type of data that is utilized in Power Query which represents the difference in the two DateTime values. Duration is the combination of four numbers:

days.hours.minutes.seconds

This is the way you interpret the above data. For the perspective of an individual, you do not need them to search for information like this. There are methods that can determine each component that is an amount of time. If you select the Duration menu , you'll be able to see that you'll be able extract the number of seconds, minutes or hours, days, and years from it.

to assist with calculating the age in years such as, say, it is easy to select Total Years.

Note that the length of the program is measured by days and then subdivided into 365, providing you with the annual figure.

Rounding

No one claims they're 53.813698630136983! They use the term 53, then they round it down. It's simple to select Rounding or Round Down on the Transform tab.

This will give you the age in years:

It's also possible to cleanse other columns if you'd like (or maybe you've made use of transformations within the Transform tab to avoid the creating of additional columns) This column can be renamed as Age column and Age:

Things to Know

  • Refresh The age calculated in this way is updated every time you are refreshing your database. Each time, it will match the birthdate with the date and the time of the refresh. This method is an initial calculation of the age. If you're in need of this calculation performed dynamically using DAX here I provided a method you could apply.
  • The motive for Power Query: Benefits of doing calculations on age in Power Query is that the calculation is done when you refresh your report, using an instrument that makes the calculation simpler, and there's no additional cost of doing it with DAX to gauge the time of runtime.
  • Another case where these are not meant to calculate age but rather start at the date of birth. This could be used for the age of inventory of items, as well as the distinction in dates between dates one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He holds a BSc of Computer engineering. He holds greater than twenty years' working experience in data analysis data and BI, database development, and programming mostly using Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years consecutively (from 2011 to the present) for his commitment towards Microsoft BI. Reza can be found as an active writer and co-founder of RADACAD. Reza is also co-founder and organizer of Difinity Conference which is held 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 some ebooks on MS SQL BI and also is working on a few more. The author was a frequent member on online technical forums such as MicrosoftDN as well as Experts-Exchange and was the moderator of MSDN SQL Server forums, and is an MCP, MCSE, and the MCITP for Business Intelligence. He is the creator of the New Zealand Business Intelligence users group. In addition, he is the creator of the famous book Power BI from Rookie to Rock Star, which is free and has more than 170 pages of content as well as it is a element of Power BI Pro Architecture published by Apress.
It is an International Speaker at Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's passion is to help users find the right solutions for their data, and he's a Data enthusiast.This post was published in Power BI, Power BI from Rookie to Rockstar, Power Query and is filed into Power BI, Power BI from Rookie to Rock Star, Power Query. Bookmark the permalink.

Post navigation

Share visual pages on various security groups inside Power BIAge Calculation in Years which can be used for calculate Leap Year in Power Business Intelligence by using Power Query

Comments

Post a Comment

Popular posts from this blog

pace-converter

Converter Parts Per Million (ppm) Parts Per Million (ppm) Converter for gases. The converter calculates the amount of gas measured (in unit of [ppm]) into units [mg/m 3 [mg/m 3] and then in reverse. The unit ppm is employed throughout the world and in various ways. The use of ppm ought to be noted in the fields that require input below to allow it to be converted to the appropriate unit. To learn more about the usage of ppm, you will find the details below.In this area the entry field for Molecular Weight will be displayed. Choose the drop-down menu or input the molecular amount in the gas. When the weight in molecular type of the gas isn't evident for you, try our Molecular Weight Calculator.By entering your numbers in Formulain Units [ppm] or using the formula in formula in units [ppm or that increment, mg/m 3 or mg/m3This number will be transformed instantly and shown in lower right. The significance of the number is calculated automatically. It is possi...
Read more

Random Number Generator

Image
How to Generate Random Numbers on the TI-84 Plus In This Article By Jeff McCalla, C. C. Edwards Your TI-84 Plus calculator has a enormous number of digits in a set, also known as the random number table that it uses in order to generate random numbers. Generating random integers To generate random integer ,follow these steps: To start the randInt Wizard on the Math PROB menu, press If you're using a T-84 Plus, there is no wizard to rundint. To utilize the command, you need to know the format: randint(lower higher, upper elements]).A wizard can make entering data easy. A wizard arranges the information entered in a way that is compatible with the syntax of the command. Look up the wizard on the screen that opens. Select the lower limit, and the upper limit you would like for your number of randoms to be.You would like a random number of 1 to 100. Press [ENTER] or the down-arrow key to navigate through the next screen in the wizard. Enter n t...
Read more

Bound Meaning In Tamil

Image
  Bound Meaning In Tamil - தமிழ் பொருள் விளக்கம் " Bound " தமிழ் மொழிபெயர்ப்பு, பொருள், வரையறை, விளக்கம் மற்றும் தொடர்புடைய சொற்கள் மற்றும் புகைப்பட எடுத்துக்காட்டுகள் - நீங்கள் இங்கே படிக்கலாம். Bound   ♪ : / bound / பெயரடை : adjective கடமைப்பட்டுள்ளது கட்டாயப்படுத்தப்பட்டது கட்டாயப்படுத்தப்பட்டது பிளாகரால் இயக்கப்படுகிறது நிச்சயமாக கட்டப்பட்டது கட்டுண்டது கடமைப்பட்டுள்ளது கட்டுண்டது தாவி செல்லவும் தாவி செல்லவும் எல்லை நிச்சயம் பெயர்ச்சொல் : noun பாய்ச்சல் தாவி செல்லவும் குத்துதல் எல்லை வரம்பு எல்லை சுற்றளவு நிச்சயம் வினை : verb பிணைப்பு கட்டுண்ட அளவு எல்லை வரையறை அளவை இகல் இறுதி கணக்குவழக்கு கரை குதித்தல் குதிப்பு கையுங்கணக்கும் பாய் பாய்ச்சல் மட்டம் எகிருதல் துள்ளுதல் தாவி செல்லவும் தாவி செல்லவும் முன்னோக்கி செல்லவும் வரம்புகளை அமைக்கவும் எல்லையாக இருங்கள் மேலே குதிக்கவும் அளவு முடிவடையும் காத்திருங்கள் படம் : Image விளக்கம் : Explanation குதிக்கும் முன்னேற்றங்களுடன் நடக்கவும் அல்லது இயக்கவும...
Read more