Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method to calculating the age. However, since DAX is the largest and most widely used language usedin numerous calculationsin Power BI, many do not realize this function in Power Query. In this blog post , I will describe how simple for you to calculateAge using Power BI in Power BI. This methodis exceptionally helpful in situations where you need in order to calculate the age calculationscan be carried out by calculating the row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table, which is part of the AdventureWorksDW table which contains the birthdate column. I've removed a few columns that don't need to be there for clarity;

In order to calculate how old every client will be, all you'll need to do is:

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

That's about it. This is the method to calculate what's the ratio between Birthdate column, as well in the current date and time.

However, the date that appears in the Age column doesn't actually appear to be an age. This is due to its actual time period.

Duration

Duration is a specific type of data that is used to calculate the duration of Power Query which represents the variations in two DateTime values. Duration is a combination of 4 numbers

days.hours.minutes.seconds

This is how to read the above values. From the point of view of the user it's not required of them to be able to discern the meaning of these numbers. There are methods that can be used to calculate each fraction of the time. If you click on the Duration menu you'll be able see the quantity of minutes, seconds, hours, months and days from it.

To aid with calculating the age in years like, for example, it is simple to choose Total Years.

It is crucial to remember this: the measurement is in day, and following that, divided by the number of days, to provide you with an annual number.

Rounding

At the final point, nobody says that they are 53.813698630136983! They say 53, which is lower than the original. It's simple to select Rounding, and then the round down option on the Transform tab.

This will provide you with the years of age:

After that, you can clear the other columns, If you'd like (or the reason could be because you utilized transformations in the Transform tab to avoid creating new columns) The column could be named something like Age Column Age.

Things to Know

  • Refresh The age that is calculated in this manner will be updated when refreshing your data. Each time, it's going to match the date of birth to the date and moment when the data refresh took place. That method can be described as an algorithm used for pre-calculating the age. If, however, you need the calculation of the age to be carried out dynamically using DAX Here's how I explained the method which you can employ.
  • What's the cause? Power Query Benefits of using age calculation using The benefit of using age calculation with Query is that the calculation is carried out while you refresh your report. The calculation is performed using an application which makes the calculation simpler, and there is no additional overhead in calculating it using DAX as a measurement of running time.
  • Other scenarios This is not an alternative method to calculate age solely by birthdate. This can be used in order to calculate the stock age for products and also to determine the differences 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 has a BSc of Computer engineering. He has more than 20 years' worth of experience in the area of data analysis, programming, databases, BI and development mostly using Microsoft technologies. He has been an official Microsoft Data Platform MVP for nine years (from 2011 until now) for his dedication towards Microsoft BI. Reza is a prolific author and co-founder of RADACAD. Reza is also co-organizer and co-founder of the 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 couple of publications on MS SQL BI and also is working on other books. He was also a regular participant on technical forums online like MSDN and Experts-Exchange and was moderator on the MSDN SQL Server Forums as well as holding the MCP and MCSE as along with the MCITP for BI. He is the leader of the New Zealand Business Intelligence users group. In addition, he's also the writer of the highly well-respected Book Power BI from Rookie to Rock Star, which is free and contains over 17000 pages of data and Power BI Pro Architecture, which is the Power BI Pro Architecture published by Apress.
He 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 purpose is to help you discover the most effective data solution. He is a Data enthusiast.This blog post was posted in Power BI, Power BI from Rookie to Rockstar, Power Query and was tagged as Power BI, Power BI from Rookie to Rock Star, Power Query. This entry was filed under Power BI. The following entry was filed under Power BI.

Post navigation

Share various visual pages by using various security groups within Power BIAge's Year Calculation that is used for Leap Year in Power BI using Power Query

Comments

Post a Comment

Popular posts from this blog

10 flowers name In english

  flowers name  |10 Flowers Name In Hindi-English| Name Of Flowers List | flowers name in hindi 10 flowers name In english 1.   Heather Introduce your baby Heather into the world of the living and watch her grow in the same way as this blooming purple plant.   In Scotland, this plant grew in barren lands, bringing life to the rocky grounds.ADVERTISEMENT 2.   Holly "Are you a Christmas baby?" I asked every person who's met the Holly.   Holly bushes have a punchy red berries that make them instantly  recognizable  they are loved during the holiday season.   The name originates directly from the Old English word "holegn," which translates to "to prick." 3.   Ivy In Ancient Greece,  newlyweds  would be gifted an ivy wreath to symbolize their faithfulness to one another.   The Old English name still represents faith.   We're certain that your sweet Ivy will be as  stylish and cool just like Blue Ivy Carter . 4.  ...
Read more

Bhagavad Gita in English & bhagavad gita as it is pdf

  Bhagavad Gita in English  &  bhagavad gita as it is pdf The  The bhagavad gita as it is  is, an ancient Indian text is a significant piece in the Hindu tradition.   It contains both philosophy and literature.   In the period between 350 BCE and 200 CE it was the time that the Bhagavad Gita was composed.   The source of the Bhagavadgita isn't known unlike the Upanishads as well as the Vedas. Bhagavad Gita pdf as It Is is a sacred text composed in His Grace A.C. Bhaktivedanta Swaami Prabhupada. The  Bhagavad Gita , an immortal text of spiritual insight, is from ancient India.   Gita is the name of both the title of the song and also the word.   Bhagavad is an Hebrew word which means God.   In the Bhagavad Gita often refers to the Song of God.   In the  Bhagavadgita is the result of a synthesis between Hindu notions of dharma, and theistic Bhakti.   It also highlights the ideals of yoga, moksha.   The tex...
Read more
Image
What's an angle ? An acute, obtuse angle An angle is a shape created by two rays that share the same starting point, also known as vertex. vertex. You might be wondering what are the benefits of angles? It is possible to calculate the height of a structure when you know the distance from you, and also the angles between the top of the building and the ground. Utilizing the same method you can figure out the size of the moon or, with the right tools, calculate the diameter of our planet. If you throw something and wish to determine the distance it will travel, you'll have to be aware of the angle at which you throw it at. There are many other areas that require angles, however at present, we'll look at the basics of geometry. Angles are divided according to their dimensions: WHAT IS A FREQUENCY CONVERTER ? A frequency converter, often called a power frequency converter , is an instrument that receives the power of the source, typically at a frequency of 50 or 60 ...
Read more