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 several functionsin Power BI, many do not understand the functionality available in Power Query. In this article I will describe how simple it is to calculateAge within Power BI and Power BI. It is a great methodis extremely helpful when the computation of an agecan be calculated on a row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table that is one of AdventureWorksDW table that is one of the columns that is a time column. I've removed a few additional columns so that it is simpler to read.

In order to calculate the age of every client, you need is:

  • In Power BI Desktop, Click on Transform Data
  • In the Power Query Editor window; begin by choosing the column titled Birthdate.
  • Click on the add Column Tab found under the "From Date & Time" section, and under Date Select the age range.

This is all there is to it. This can calculate an amount which is the sum of the Birthdate column as well as the actual date and time.

However, the age appearing in an Age column, however, it does not seem to look like an actual age. This is because it's the duration.

Duration

Duration is a specific kind of data format that is found with Power Query which represents the difference between two DateTime values. Duration is a mixture comprising four figures:

days.hours.minutes.seconds

These are the values you'll see in the above values. From a personal point of view, they shouldn't have to seek out specifics such as the ones mentioned above. There are methods that could capture every second of the time. Utilizing the Duration menu option, you'll see the number of seconds, minutes, hours, and years out of it.

For calculating the age in years like, for instance it is as simple as going to Total Years.

The duration is determined in days and then divided by 365, giving you the value for the year.

Rounding

And lastly, no one says to be 53.813698630136983! They claim it's 53, but with a rounding down. It is easy to select the Rounding option and then round down in the Transform tab.

This will let you know your age in years:

Then, you can cleanse other columns, at your discretion (or you could have used transformations in the Transform tab to stop creating new columns) The column could be named column: Age.

Things to Know

  • Refresh The estimated age by this method will be updated every time you are refreshing your data. Each time, it'll compare your birthdate with the date and moment of the refresh. This method is a pre-calculation of an age. If you require the calculation of an age to be executed dynamically employing DAX, here's how I have described a technique you can use.
  • Arguments for choosing Power Query: Benefits of performing age calculations in Power Query is that the calculation is made at the time of refreshing your report. This is achieved by making use of a tool that makes calculation easier, and there's no need for the added cost of doing it using DAX as a measurement of running time.
  • Another possibility is that it cannot be utilized to calculate the age based on birth date. It could be used to determine inventory of goods as or the difference in 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. There are more than 20 years' experience in data analysis , database programming, BI, and development specifically focused specifically on Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years (from 2011 until now) due to his devotion towards Microsoft BI. Reza has a long-running career as a writer and co-founder of RADACAD. Reza is also co-founder and coordinator of Difinity Conference 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 wrote a couple of books about MS SQL BI and also is writing more books. He is also a regular participant in online forums on technical issues like MSDN and Experts-Exchange , as well as moderator for MSDN SQL Server forums, and is an MCP and an MCSE and MCITP for Business Intelligence. The director is of the New Zealand Business Intelligence users group. Additionally, he is the writer of very popular workbook Power BI from Rookie to Rock Star, which is freely available and contains more that 17000 pages of content and another book titled Power BI Pro Architecture published by Apress.
Speakers are an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday, as well as SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's passion is to help you find the most efficient solutions to deal with data. And is an avid Data enthusiast.This blog post appeared in Power BI, Power BI from Rookie to Rockstar, Power Query and closely related to Power BI, Power BI from Rookie to Rock Star, Power Query. This is a great resource for you to bookmark.

Post navigation

- Share various visual pages using different security groups in Power BIAge's Year Calculation that works for Leap Year in Power BI by using Power Query

Comments

Post a Comment

Popular posts from this blog

power-converter

Converter Parts Per Million (ppm) Parts per Million (ppm) Converter for Gases This converter calculates the estimated number (in increments of [ppm[ppm]) and converts it into any of these units: (mg/m 3. [mg/m 3] ] and vice reverse. The unit ppm may be used in a variety of branches in a variety of ways. The use of ppm must be specified in the fields that follow in accordance with how it is converted into the appropriate unit. For more details on the meaning of ppm study the definition below.In this area, you can input of Molecular Weight . You can choose the drop-down menu, or you could input your own values to calculate the mass molecular of gas. If you aren't sure about the molecular weight to you, you can try our Molecular weight Calculator.When you enter either either or the calculation in increments [ppm] that are (mg/m 3 or [mg/m3The amount is converted instantly and displayed in the lower part of the screen. The significance of the calculation is de...
Read more

random number generator

Random Number Generator Make use of the generator to get an absolutely random digitally secure number. It creates random numbers that can be employed when precision of the result is important in shuffles of deck of cards in a game of Poker or drawing numbers to win giveaways, lottery or sweepstakes. What is the best way to select an random number between two numbers? You can make use of this random number generator for you to generate a reliable random number from any two numbers. For example, to generate an random number from one to 10 including 10, you need to enter 1 first in the input , and 10 in the second field then press "Get Random Number". Our randomizer will choose one of the numbers 1 to 10 randomly. To generate an random number between 1 and 100, utilize similar methods with 100, but it's in the 2nd field on the randomizer. To simulate a roll of dice the number range should be between 1-6 to simulate a typical six-sided dice. To generat...
Read more

pkdeveloper.in/ms-excel-formulas-list-pdf

MS Excel Formulas List PDF | MS Excel Formula & Functions Basics MS Excel Formulas List MS Excel Formula means is calculated by adding up numbers, then subtracting the total by the sum of the numbers. The following functions can be utilized to calculate the mean using Microsoft Excel: AVERAGE- Returns the average of numbers. Formulas are expressions that calculates the value of the cell. Excel already includes functions that can be utilized to build formulas that are pre-defined. MS Excel Formula & Functions Basics Formula construction: Formulae and functions begin with the letter =. Choose a range of cells or cell group that will be utilized in the formula by using your mouse Plus Add *Multiply, Subtract/Divide are the formulae's operators. BODMAS rules apply to the arithmetic (Brackets Over Division, Multiplication, Addition and Subtraction). Don't type variables into formulas (such for tax rate). Instead, place y...
Read more