Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method for calculating the age. However, since DAX is the primary language usedin numerous calculationsin Power BI, many are not aware of this feature in Power Query. In this article I'll explain how simple it is to calculateAge within Power BI using PowerBI. This methodis extremely useful when your estimation of your agecan be calculated using an earlier calculated row-by-row row basis.

Calculate Age from a date

Below you'll find the DimCustomer table, which is an integral part of the AdventureWorksDW table, which is an entry point for the birthday column. I've removed a few of additional columns making it simpler to read.

To calculate your age for each client All you have to do is:

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

That's that. this is the method you calculate an amount which is the total of the column for Birthdate column, and the current date and time.

But, the age that appears in"under" the Age column, but doesn't appear to be a real age. It's due to the fact that it's an actual time period.

Duration

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

days.hours.minutes.seconds

and that is how you consider the above data. For the view of the user, it's not their responsibility to read the specifics of this. There are techniques that can extract every bit of information from the period. By choosing the duration menu you'll find that it is possible to extract the number of seconds and minutes as well as days, hours and years from it.

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

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

Rounding

No one declares they are 53.813698630136983! They use the word 53 which is an arbitrary number that is rounded down. It is easy to select Rounding and Round Down from the Transform tab.

This will give you an indication of the age you've reached in your years.

Then, you can clean the other columns, should you like (or perhaps you've made use of transformations with the Transform tab, avoiding having you create new columns) And name this column Age:

Things to Know

  • Refresh The age that is calculated this way will get changed each time you're refreshing your data. Each time, it compares the date of birth to the date and timing at the time of refresh. This method is an algorithm for pre-calculating an age. If you want the calculation to be done dynamically with DAX this is the way I explained the method you could apply.
  • The reason behind Power Query: Benefits of performing an age calculation using Power Query is that the calculation is done at the time of refresh of your report. It is an instrument which makes the calculation faster and more efficient, and there's no added cost when it is calculated using DAX to measure runtime.
  • Alternative scenarios It's not utilized to calculate the date of birth. this can be used for the age of inventory at the stock level and the differences between two dates or 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 twenty years' experience in the field of data analysis and databases, BI, and development mostly in Microsoft technologies. He has been a Microsoft Data Platform MVP for 9 consecutive years (from 2011 to the present) in recognition of his passion with Microsoft BI. Reza is an experienced blog writer and co-founder and the editor for RADACAD. Reza is also co-founder and 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 was also a frequent forum participant on online forums for technical matters like MSDN and Experts-Exchange and was also the moderator of MSDN SQL Server forums, and is an MCP and MCSE and an MCITP of Business Intelligence. He is also the leader of the New Zealand Business Intelligence users group. In addition, he is the co-author of the well-known guidebook Power BI from Rookie to Rock Star, which is free and contains more than 1800 pages of material and 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 love is helping users find the right data solution. He is an avid Data enthusiast.This article was published by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is filed with Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was posted in Power BI. You can follow any responses to this entry through the RSS feed.

Post navigation

- Share different visual pages using different security groups in Power BIAge's Years Calculation that can be used for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

KGF Full Form The complete form of the KGF?

  KGF Full Form  The full form of the KGF? It is the mining zone in Bangarpet Taluk, Kolar District situated in Karnataka.   It is the largest Indian mining facility for gold.   It was shut down in 2001 due to increase in costs for production and production, as well as fewer gold deposits. It was the second-deepest mine place in all of the earth, and was famous for its earlier production of the precious metal gold.   It's also known as "Little England" because of its stunning views and its dry climate.   It's the home of English bungalows and roads which are very well-designed.   Kolar dates back to in around the year 2100 AD and is much older than Bangalore.   Ganga founded Kolar as her capital city within her city and was also Governor for Mysore and Coimbatore. The story of Kolar Gold Fields|  full form of kgf K  The olar Gold Fields  (KGF) Also known as "Little Britain" is located in the South Indian Kolar District, Karnataka. ...
Read more

energy-converter

Image
What's the meaning of angles ? The angle is narrow, acute, or narrow. An angle is the shape formed by two Rays. They share a point of origin which is referred to as the vertex. There is a questionto ask What are the benefits of angles? It is feasible to figure out the height of a structure by knowing how far it is from your location, and angles between the surface and the top. Utilizing the same method, there is a way find out the size of moon's diameter, or, if you have the proper equipment, the size that our earth is. Also, if you throw something and you want to determine how far it travels, you'll have to understand the angle at which it is being thrown. There are many different applications where angles are beneficial however, for the time we'll focus on the basics of geometry. Angles can be classified into their dimensions: WHAT IS A FREQUENCY CONVERTER ? Frequency converters are often known as a power-frequency converter. It is one that takes in the po...
Read more

BMI Calculator

BMI Calculator Understanding BMI can assist adult both women and men in understanding their overall health. Use the BMI calculator below to calculate your body mass index. BMI calculator is below. It will determine the body mass index of your body by entering your height and weight. BMI calculator BMI calculator employs the following BMI formula to determine: (lb) (lb) (Lb) + (Height (in))2 703. Understanding Your Body Mass Index The reason is that your BMI is less than 18.5: Your BMI is believed to fall within the category of overweight. Take note that being overweight in your BMI calculation could result in certain health risks. Consult your healthcare physician for more information regarding BMI calculation. The reason could be your BMI is in the area 18.5-24.9: Your BMI is considered to be normal. Healthy weight can reduce the risk of suffering from serious ailments and indicate that you're near to achieving what you want to achieve in fitness. It is poss...
Read more