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 main language usedin numerous calculationsin Power BI, many are unaware of the function that is available in Power Query. In this article, I'll describe the process of how to calculateAge using Power BI with Power BI. The methodis extremely useful when the estimation of the agecan be calculated using an earlier calculated row by row basis.

Calculate Age from a date

Below, you will see the DimCustomer table, which is a an integral part of the AdventureWorksDW table which functions as the birthdate column. I've removed some of the columns in order for easier understand.

In order to calculate your age for each of your customers all you have to do is to:

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

That's it. this is how you calculate an amount which is the sum of the Birthdate column, and the current date as well as time.

But, the age that appears in"Age" but, since it's under the Age column, but it doesn't appear to be the actual age. It's because it's actually a length.

Duration

Duration is a distinct kind of data within Power Query which represents the difference of the two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

and that is how you can determine the meaning of the data. For the view of the user, it's not necessary for them to look up the details of this. There are methods that could get every part in the time. By selecting the Duration menu, you'll discover that you can to calculate the amount of seconds and minutes as well as days, hours and years from it.

To help in calculating the age in years for example the age of a person, simply click the Total Year:

It is important to note that the duration is measured in days . It is after that subtracted by the amount of days for the year's total.

Rounding

It's the truth, no person declares that they are 53.813698630136983! They use the word 53 which is rounded down. It's easy to choose Rounding and round down on the Transform tab.

This will show you how old you are:

You can then cleanse the other columns, if you wish (or perhaps you've made use of transformations by using the Transform tab to not have inventing new columns) And name this column: Age

Things to Know

  • Refresh The age calculated this way is updated each time you are refreshing your information. And each time it will be comparing your birthdate with the date and moment of the refresh. This method is a pre-calculation for an age. If, however, you need the calculation to be made dynamically using DAX here is how I described the method you could employ.
  • The motive for Power Query: Benefits of doing an age calculation using Power Query is that the calculation is made in the course of refreshing your report. This is done using an instrument that makes the calculation more efficient and speedier, and there's no additional cost for calculating it with DAX for measuring the time.
  • Alternative scenarios It can't be utilized to calculate the age of a person based on their birth date. This can be used to calculate inventory-level ages for inventory items and for the variations between two dates and times from one another.

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 has more than twenty years' experience in the area of data analysis, databases, BI, and development mostly in Microsoft technologies. He is a Microsoft Data Platform MVP for nine consecutive years (from 2011 until the present) because of his love in Microsoft BI. Reza is a prolific blog writer as well as the co-founder and director of RADACAD. Reza is also co-founder as well as 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 a few books on MS SQL BI and also is working on additional books. He was also a regular participant in online forums dealing with technical issues like MSDN and Experts-Exchange and was also the moderator of the MSDN SQL Server forums, and is an MCP and MCSE and an MCITP of Business Intelligence. He is the founder of the New Zealand Business Intelligence users group. The group also is author of the well-known book Power BI from Rookie to Rock Star, which is free with more than 1800 pages of material and A Press publication called 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 goal is to assist users find the right data solution. He is a Data enthusiast.This post was published by Reza in Power BI, Power BI from Rookie to Rockstar, Power Query and is filed under Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. Please leave a comment.

Post navigation

- Share Different Visual Pages with different Security Groups within Power BIAge Calculation in Years that is used to calculate Leap Year in Power BI through Power Query

Comments

Post a Comment

Popular posts from this blog

the full version of kgf and story

  an all-inclusive version of kgf  and story The teaser for KGF: Chapter 2 is set to be released on the eighth of January.   Let's review KGF Chapter 1 and then see what  viewers can anticipate in the follow-up. The winter of 2018-19 was a busy time for Yash as well as Rocky Bhai.   The tall, handsome and handsome star's informal dialogues from the film  KGF Chapter  1 were the talk of social media.   It was a moment that defined pop culture in Indian film's history.   We expected nothing less in the beginning, as prior to the debut of KGF: Chapter 1 it was the focus of a massive buzz surrounding the film , which was one of the highest-priced Kannada film ever made.   The film was released in more than 1800 theatres. KGF Chapter 1 delivered big time, becoming the biggest blockbuster to be an instant hit throughout the United States.   The film was an visually pleasing masala  entertainment with adrenaline-pumping action scenes ...
Read more

KGF Full Form What is the full format of KGF?

  KGF Full Form  What is the full version of the KGF? This is the mining zone of Bangarpet Taluk, Kolar District situated in Karnataka.   It is the biggest Indian gold mine.   However, it was closed in 2001 because of the increased cost of production, less production and the loss of gold deposits. It was the second deepest gold mine in the world. It was famous for its past gold mining.   It's also known as "Little England" because of its spectacular landscape and dry climate.   It is the home of English bungalows and well-designed roads.   Kolar was established in two centuries AD and is more ancient than Bangalore.   Ganga established Kolar the capital of her city, and also was the chief of Mysore as well as Coimbatore. The tale of Kolar Gold Fields|  full form of kgf K  The olar Gold Fields  (KGF) Also known as "Little British" is located in the South Indian Kolar District, Karnataka.   It's located about 100 kilometers dista...
Read more

What is the Full Form of SIT?

  Composition of SIT The court makes the final decision on SIT and it is tasked to provide advice and gather  the evidence. of evidence.   It's a distinct process from CBI in this respect.   SIT is a temporary solution. SIT is a temporary solution SIT is a temporary measure, and SIT is over when the case is settled. It is comprised of highly respected  police officers who make up police officers from police officers of the  the police department .   The departmental head of the police force state-wide and eminent former police officers and civil servants are usually assigned to collect evidence for the investigation. Types of Cases that are appointed to the SIT(  sit full form ) There are many instances that could be handed over to SIT.   These could be cases that are of a high-profile nature, and generally being the case, when the local police force is unable to come to an agreement. Objectives of SIT The primary goal of SIT is to carry out ...
Read more