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 well-known language usedin several functionsin Power BI, a lot of users don't realize the function available in Power Query. In this article I will explain how easy it is to calculateAge within Power BI with Power BI. This methodis very beneficial in situations where the calculation of the agecan be completed on a pre-calculated row-by-row basis.

Calculate Age from a date

Here is the DimCustomer table that's one of AdventureWorksDW table, which is one of the columns that is a time column. I've removed a few additional columns, to make it easier to comprehend.

In order to calculate the age of every buyer, all you have to do is:

  • In Power BI Desktop, Click on Transform Data
  • Inside the Power Query Editor window; begin by choosing the column titled Birthdate.
  • Click on the Add Column Tab that is located in the "From Date & Time" section. Also, under Date choose the appropriate age range.

That's all there is to it. It can calculate an amount that is equal to the sum of the column for birthdate, Birthdate column as well as the present date, as well as the time.

However, the age that appears in this Age column, but it doesn't really look like an age. It is due to the fact that it is the duration.

Duration

Duration is a special kind of data format used within Power Query which represents the distinction in two DateTime values. Duration is a mixture with four values:

days.hours.minutes.seconds

It's exactly what you'll observe in the following values. From a personal viewpoint, they shouldn't have to seek out specifics such as the ones above. There are methods to capture every second of the time. When you select the menu option Duration, you'll notice the amount of seconds to minutes, hours, days, and years.

For calculating the age in years like, for instance it's as easy as going to Total Years.

The duration is calculated in days , then divided by 365. This gives you the value for the year.

Rounding

No one has claimed they are 53.813698630136983! they claim 53, and then round it down. You can select the rounding option, and then round down the Transform tab.

This will show you that you're old enough to be

It is then possible to purify other columns if desired (or you could have used transformations in the Transform tab to avoid making new columns.) You could name this column as Age.

Things to Know

  • Refresh: The age calculated by this method is updated each time you are refreshing your dataset. Each time, it will compare your birthdate with the date and moment of the refresh. This method is pre-calculating an age. If, however, you require the calculation of an age to be done dynamically with DAX, this is how I have described a technique you could use.
  • Reasons to choose Power Query: Benefits of performing age calculations in Power Query is that the calculation takes place during the process of refreshing your report. This is performed by making use of a tool that makes calculations much easier and there's no need for the added cost of calculating it with DAX as a measure 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 and the difference between two dates and times one another.

Video

REZA RAD

TRAINER, CONSULTANT, MENTORReza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc from Computer engineering. The engineer has over 20 years' experience in data analysis , database, BI, programmingand development with a focus in Microsoft technologies. He is an official Microsoft Data Platform MVP for nine consecutive years (from 2011 until today) because of his commitment towards Microsoft BI. Reza is known as an prolific author and co-founder of RADACAD. Reza is also the co-founder and co-organizer of the 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 some others, In addition, he was a frequent participant in online forums on technical issues , such as MSDN and Experts-Exchange . He also serves as moderator for MSDN SQL Server forums, and is an MCP , MCSE as well as an MCITP for Business Intelligence. As the group's director, he is in charge of New Zealand Business Intelligence users group. Also, he's author of the incredibly popular books such as Power BI from Rookie to Rock Star, which is freely available and contains more than 17000 pages of information . It also includes an additional 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, as well as SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's goal is to help you discover the most effective methods to manage data, and he's an avid Data enthusiast.This post was originally published by Power BI, Power BI from Rookie to Rockstar, Power Query and related to Power BI, Power BI from Rookie to Rock Star, Power Query. This article is an excellent source to bookmark.

Post navigation

- Share different visual pages by using Different Security Groups that PowerBIAge uses for Year Calculation that works for Leap Year in Power BI using Power Query

Comments

Post a Comment

Popular posts from this blog

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 utilized when reliability of results is critical in shuffles of deck of cards during playing Poker as well as drawing numbers to win sweepstakes, giveaways or lottery. What's the best way to select the best random number between two numbers? It is possible to use this random number generator for you to generate a true random number from any two numbers. For example, to obtain a random number from one to 10 including 10, you need to enter 1 first in the input field, and 10, in the second input field, and finally click "Get Random Number". Our randomizer will choose one of the numbers 1 to 10 randomly. To generate a random number between 1 and 100, use the same method for 100, however, it's within the 2nd field of the randomizer. In order to simulate a roll of a dice the range of numbers must be between 1-6 to ...
Read more

Parts Per Million (ppm) Converter

Converter Parts Per Million (ppm) Parts per Million (ppm) Converter for gases. The converter calculates that the quantity of gas's volume in ppm units and converts it into measurements [mg/m 3[mg/m3] and that reverses. The unit ppm is employed in a variety of branches in various ways. The usage of ppm must be specified in the fields below to input, in order to be converted to the proper unit. For more details about the usage of ppm, read the instruction manual below.In the field labeled "Molecular Weight, you will find the entry field for this field. Molecular Weight can be selected by selecting the drop-down menu. Or put in the molecular mass within that gas. Should the value of molecular weight is not known for you, then test our molecular mass Calculator.When you enter or one of the or the formulas, either the formula in units of [ppm] or in units of [mg/m] 3]. The value is converted instantly and displayed in the lower right. The importance of the ...
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 numbers, then subtracting the total by the total of those numbers. The following functions are available to calculate the mean in Microsoft Excel: AVERAGE- Returns an average of numbers. Formulas are expressions that calculates the value of the cell. Excel already has functions that can be used to make formulas predefined. MS Excel Formula & Functions Basics Formula to build: All formulae and functions begin with the letter =. Select a cell range or cell group to be used in the formula by using your mouse and Add * Multiply, Subtract/Divide are the operators for formulae. BODMAS rules are applicable to the arithmetic (Brackets Over Division and Multiplication then Addition then Subtraction). Do not enter variables in formulas (such like tax rates). Instead, place the variable in a separate c...
Read more