Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, because DAX is the preferred language usedin many calculationsin Power BI, many don't know about this feature provided by Power Query. In this article, I'll describe how easy it is to calculateAge in Power BI using PowerBI. It is a methodis extremely efficient in cases where the estimation of your agecan be performed on a pre-calculated rows and basis.

Calculate Age from a date

Below, you'll find the DimCustomer table which is part of the AdventureWorksDW table. It also has an entry for birthdates. I've removed several of the columns that don't need to be for ease of read;

To calculate the time of each customer's purchase all you need to do is:

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

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

But, the age appears on the Age column, it doesn't actually appear to be an age. This is because it's an actual Duration.

Duration

Duration is an exclusive type of data format in Power Query which represents the variations between the two DateTime values. Duration is a mix of four values:

days.hours.minutes.seconds

That's the way you see the numbers above. But from the standpoint of the user it is not their responsibility to understand the meaning of numbers like that. There are ways you can get every segment of the duration. using the Duration menu option you'll be able to take the number of seconds, minutes, hours, days , and years from it.

To aid in calculating the age in years with an example, it is easy to select Total Years:

The duration is calculated in days . It was then divided in 365 to provide the annual amount.

Rounding

The bottom line is that nobody declares your age as 53.813698630136983! They use 53 which is reduced to a lower number. You can select Rounding and Round Down in the Transform tab.

This will give you an indication of that you're old enough to be

You can then cleanse the other columns, if desired (or there could be because you made use of transformations on the Transform tab, to prevent creating new columns) You can also name this column; Age:

Things to Know

  • Refresh The data's age that is calculated this way will be refreshed when you refresh your database. Each time, the system will be capable of comparing the birth date to the date and the date and time of refreshing. This method can be described as an algorithm to calculate the age. If you'd like the calculation of age to be performed dynamically with DAX here's how I have described the method I would recommend making use of.
  • The reasoning behind Power Query: Benefits of using age calculation with Power Query is that the calculation is done when you refresh your report. This is done by using an application that makes calculations easy and quicker, and there's no extra cost to calculate it using DAX as a measure of runtime.
  • Other scenarios They aren't meant to be used for calculation of age from birthdate. It can be used to calculate the age of inventory of items and also the differing dates and dates that differ from one other.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has an BSc with a major of Computer engineering. More than twenty years' experience working in the field of data analysis data, BI, databases development, and programming generally with Microsoft technologies. He was an Microsoft Data Platform MVP for nine years in a row (from 2011 to the present) because of his love of Microsoft BI. Reza is a prolific writer and co-founder at RADACAD. Reza is also the co-founder and organizer of the Difinity event 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 wrote some books on MS SQL BI and also is writing some other books. He was also a regular member of technical forums online such as MSDN and Experts-Exchange and was a moderator on the MSDN SQL Server forums as well as an MCP and MCSE aswell as an MCITP in Business Intelligence. He also is the leader for the New Zealand Business Intelligence users group. This group is also the writer of the book that is loved by many. Power BI from Rookie to Rock Star, which is completely free and includes more than 1700 pages of material and a second book 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 Group for Users. And He is a Microsoft Certified Trainer.
Reza's dream is to help people find the best solutions for data. he's a Data enthusiast.This post was posted under 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 is a great resource for you to bookmark.

Post navigation

Share Different Visual Pages with different Security Groups. PowerBIAge in Years Calculation , which works for Leap Year in Power BI using Power Query

Comments

Post a Comment

Popular posts from this blog

what is the full form of crpf ?

  What's what is the  full form of crpf  ? In this blog, you will be reading about  crpf full form ,crpf ka complete form  crpf full form in hindi ,crpf ki complete form.. This blog contains all the information you need in this blog.   This blog will fully explain the crpf full form, crpf ka complete form crpf full form in Hindi, crpf ki full form..   I hope that you enjoy this blog post. The CRPF is an paramilitary organization which is the largest in India. Central Armed Police Force.   Its Ministry of Home Affairs of India supervises the operations of CRPF.   Its main goal is to support the government of the state and association domain in ensuring the rule in law, peace and order within their respective regions.   It is located within New Delhi, India.   The Indian public authority has created the CRPR Foundation to help young members join the CRPF.   Sh Rajeevrai Bhatnagaris the Director General of the CRPF is in charge si...
Read more

What exactly is psc full form

  What's the  psc full form PSC : Public Service Commission PPSC stands in the form of Public Service Commission.   It's a central administrative body that manages the  exams that will be held for various categories of jobs.   Public Service Commission was set in accordance with  Article 315-323  315 to 323 of the constitution of India. For the Union, this commission is known as the  Union Public Service Commission  that is responsible for conducting exams for the purpose of securing Indian jobs at the nation level and more prestigious Central Services.   For instance, IAS is an exam that IAS  exam is administered by UPSC every year.   The exam is an entry point into Group A and B services. Whereas, at the state one, the commission is called the  State Public Service Commission  that belongs to a state, and administers exams to recruit officers to state services and to offer suggestions and guidance to the governor on...
Read more

What is the exact form of CRPF?

  What is the exact  form of CRPF ? About CRPF The  Central Reserve Police Force  was created as Crown Representative's Police on 27th July 1939.   It became known as the Central Reserve Police Force on the passage of the CRPF Act on 28th December 1949.   It has now completed 82 years.   The Force has evolved into a huge organization with 246 Bns (including 203 executive Bns and 5 VIP Security Bns 6 Mahila Bns and 15 RAF Bns 10 CoBRA Bns and as well as the Signal Bns (including 01 Special Duty Group, 01 Parliament Duty Group Special Duty Group, 01 Parliament Duty Group), 43 Group Centres , 22 Training Institutions 03 CWSand 07 AWS, 03 MWS/SWS 04 Composite Hospitals comprising 100 beds 18 composite hospitals with 50 beds and the 06 Field Hospitals. Mission The purpose of the Central Reserve Police Force Central Reserve Police Force is to help the government assure Rule of Law, Public Order and Internal Security effectively and efficiently in order to sa...
Read more