Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, as DAX is the most popular language usedin many computationsin Power BI, many do not know this function to Power Query. In this article, I'll go over how easy to calculateAge in Power BI by using PowerBI. It is a methodis extremely effective when your estimation of your agecan be calculated on a pre-calculated on a row basis.

Calculate Age from a date

Below you can view the DimCustomer table, which comprises the AdventureWorksDW table that has the birthdate column. I've removed several of the columns that aren't needed for ease of read;

In order to calculate the age of each buyer, you need is:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; begin by selecting the column with the birthdate.
  • click on the Add Column Tab, then click on"Add Column" and then on "From Date & Time" section. Under Date select the date range.

That's all there is. This will calculate an amount that is the total of the column for birthdate, Birthdate column, as well as the time and date column.

However, the age that appears in the Age column, it doesn't actually seem to be an actual age. This is because it's an actual duration.

Duration

Duration is a special kind of data format in Power Query which represents the differences of the two DateTime values. Duration is a mix of four different values:

days.hours.minutes.seconds

and that's how you take the above numbers. But from the viewpoint of the user, it's not their responsibility to know the specifics of that. there are ways that you are able to obtain every segment of the duration. When you select the Duration menu it will be apparent that you can get the duration in seconds or minutes, hours, months and days from it.

For help calculating the age in years by way of example it is simple to select Total Years:

The duration was calculated in days . It was then divided in 365days to give you the annual value.

Rounding

At the final point, no one declares your age in 53.813698630136983! They call it 53, which is reduced to a lower number. It is possible to select Rounding and Round Down in the Transform tab for it.

This will let you know what your age is in terms of years

You can then clean the other columns, should you like (or there could be that you have used transformations on the Transform tab to avoid creating new columns.) You can also name this column: Age

Things to Know

  • Refresh The information's age calculated using this method will be refreshed each time you refresh your database. Each time it is refreshed, the system will be capable of comparing the birth date with the date as well as the time in the process of refreshing. It is a method will be an algorithm that is used to pre-calculate the age. If you'd like the age calculation to be executed dynamically by using DAX here is how I explained the best way to make use of.
  • The reason behind Power Query: Benefits from using age calculation in Power Query is that the calculation takes place when you refresh your report. This is achieved by using an application that makes calculations significantly faster and easier, and there's no extra cost for calculating it using DAX as a measure of runtime.
  • Other scenarios These are not intended for the calculation of age from birth date. It is possible to calculate the age of inventory in the case of products and also the various dates and dates from one other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc with a concentration in Computer engineering. More than twenty years' expertise in the area of data analysis as well as databases, BI developing, and programming mostly using Microsoft technologies. He has been a Microsoft Data Platform MVP for nine consecutive years (from 2011 until the present) because of his love of Microsoft BI. Reza has been a prolific writer and co-founder of RADACAD. Reza is also the co-founder and organizer of the Difinity event 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 books on MS SQL BI and also is working on different books. He was also a regular participant in online forums for technical issues like MSDN and Experts-Exchange and was moderator of MSDN SQL Server forums, and is an MCP and MCSE , as well with an MCITP in BI. He is also the head for the New Zealand Business Intelligence users group. The group is also the creator of the book extremely well-loved Power BI from Rookie to Rock Star, which is totally free and comes with greater than 1700 page and a book that is titled 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 group. And He is a Microsoft Certified Trainer.
Reza's desire is to help users find the right solutions to manage data. He's an avid Data enthusiast.This post was posted with Power BI, Power BI from Rookie to Rockstar, Power Query and is classified under Power BI, Power BI from Rookie to Rock Star, Power Query. This is a fantastic resource to save to your bookmarks.

Post navigation

Share Different Visual Pages with different Security Groups. PowerBIAge in years calculation that is used for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

convert to jpeg How to Convert Images From PNG to JPG and JPG to PNG When I began my career in marketing, and I needed to include an image in one of my articles , or upload a photo to social media, its format was not a factor for me. For me, JPG and PNG were exactly the same. After I delved into what the difference is between them but I came to the conclusion that they're very different. Although neither format is necessarily better than the other -the fact is that JPG is the most suitable format for certain situations , whereas PNG is optimal for others. In a nutshell, JPG images are great to blog and social media posts because they're optimized for photography and have a smaller image size than those using PNG. Images in PNG are the best for professional web graphics and photography as they preserve their quality regardless of the amount of time you save and open them, whereas JPG images diminish in quality every time you open and save them. The goo...
Read more

what is the full form of crpf ?

  What's the  full form of crpf  ? In this blog, you will be learning about  crpf full form ,crpf full form,  crpf full form in hindi ,crpf the complete version.. You will find all the information you need in this blog.   This blog is completly explain about crpf full form,crpf ka full form, crpf full form in hindi,crpf ki full form..   I hope that you enjoy this blog. The CRPF is an paramilitary force that is the country's largest Central Armed Police Force.   Its Ministry of Home Affairs of India is the one who oversees the operation of the CRPF.   Its primary goal is to support the states and the associations' domain governments in ensuring the rule of law and peace in their respective regions.   It is located in New Delhi, India.   India's public authority established the CRPR Foundation to help young people join the CRPF.   Sh Rajeevrai Bhatnagar, Director General of CRPF has been in charge since July of 2017. MONTHLY SALA...
Read more

BMI Calculator

Mayo Clinic BMI and waist circumference calculator BMI is a measure of body mass. (BMI) is a mathematical formula that takes into account height and weight to measure body fat. If you have a high BMI is associated with an increased risk for chronic conditions like heart disease and high blood pressure and type 2 diabetes in adulthood. BMI provides a reasonable estimate of body fat in the majority people. However, it's not without its flaws. For instance, BMI may: Underestimate body fat for individuals who are elderly or have low muscle mass Overestimate body fat for people who are extremely strong and physically fit In addition, BMI may underestimate risk for people of Asian descent, in part because it does not take into account the risk conferred by excess abdominal fat. Therefore, it's useful to consider waist size (measured just a little above the hipbones for people over the age of) as well as BMI. Health risks increase with increasing waist size...
Read more