Age Calculation

Age Calculation in Power BI using Power Query

Power Query has a simple method of calculating the age. However, since DAX can be the largest and most well-known language usedin several functionsin Power BI, many people 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 for situations where the computation of an agecan be done on a pre-calculated row-by-row basis.

Calculate Age from a date

Here's the DimCustomer table that's part of the AdventureWorksDW table which includes an old column. I've removed some of the additional columns and made it easier to comprehend.

In order to calculate the age of each person who purchases from you, all that you need is to:

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

It's that simple. it. This will calculate an amount which is the sum of the column for birthdate, Birthdate column as well as the actual date and time.

However, the appearance of the age within this Age column, however, it does not appear to be an actual age. This is because it's not a length.

Duration

Duration is a special kind of data format found on Power Query which represents the difference in two DateTime values. Duration is a mix with four values:

days.hours.minutes.seconds

It's exactly what you'll observe in the following values. But, from a person's standpoint, they shouldn't be required to find specifics similar to the ones listed above. There are methods that could be used to determine every minute of the time. By using the Duration menu, you'll observe the quantity of seconds, minutes, hours, days and years out of it.

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

The duration is determined by days and then divided by 365. This gives you the value of the year.

Rounding

Finally, no one claims you are 53.813698630136983! they claim 53, but with a rounding down. You can simply select the option to round and round down the Transform tab.

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

It is also possible to purify other columns should you wish (or you could have used transformations from the Transform tab to avoid making new columns) You can name this column: Age.

Things to Know

  • Refresh The age that is calculated using the method shall be updated each time you refresh your data. Every time, it will check your birthdate with the date and date at the time of refresh. This method is an algorithm for pre-calculating your age. If, however, you need the age calculation to be dynamically performed, by using DAX, here's how I have explained a procedure you can use.
  • Benefits of using Power Query: Benefits that come with age calculations made using Power Query is that the calculation is carried out when you refresh your report. This is accomplished using a program that makes calculation much simpler, and there's no need to add the cost of calculating it with DAX as a measurement of the runtime.
  • Another situation where this method is not employed to calculate the age by birthdate. This could be used for inventory of products as well as to determine 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 holds a BSc from Computer engineering. There are more than 20 years' experience in data analysis , database programming, BI, and development that is primarily focused around Microsoft technologies. He has been a Microsoft Data Platform MVP for nine years in a row (from 2011 to the present) due to his dedication to Microsoft BI. Reza is an incredibly prolific writer and co-founder of RADACAD. Reza is also co-founder and director 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 several books on MS SQL BI and also is writing more books. He is also a regular participant in online forums on technical issues , such as MSDN and Experts Exchange, as well as moderator of MSDN SQL Server forums, and holds the MCP as well as MCS as well as MCITP for Business Intelligence. He is the director of the New Zealand Business Intelligence users group. Also, he's the author of the highly acclaimed workbook Power BI from Rookie to Rock Star, which is available for download for free and includes more that 17000 pages of data and an additional book called 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 along with SQL User Groups. And He is a Microsoft Certified Trainer.
Reza's passion is to help you discover the most effective solutions for your data. 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 resource for you to bookmark.

Post navigation

Sharing Different Visual Pages using different security groups within Power BIAge's Year Calculation that works for Leap Year in Power BI with Power Query

Comments

Post a Comment

Popular posts from this blog

Scientific Calculator

Calculator Navigate to: previous, jump to (Calcium)Next (Caleb)A basic calculator It is a calculator is a device to carry out calculations . Modern calculators usually incorporate general-purpose computing capabilities, but they're often made to fulfill specific tasks. For instance , graphing calculators focus on graph-centric maths, including trigonometry or statistical. Additionally modern calculators are larger and more mobile than computers although some personal digital assistants (PDAs) are similar in size and weight to hand-held calculators. Since calculators come in tiny and in the future, devices that are as small as credit cards can perform the exact calculations that are currently used in large scientific calculators. Another option for development is a handheld computer, where the notations aren't made by buttons, but printed on a screen. Thus, the need for buttons would be eliminated and the total dimensions of the device would be further reduced...
Read more

random number generator

Random Number Generator Use the generator to get an absolutely randomly digitally secure number. It generates random numbers that can be employed when accuracy of the numbers is vital in shuffles of deck of cards during a game of Poker as well as drawing numbers for sweepstakes, raffles, or giveaways. What's the best method to choose a random number between two numbers? You can use this random number generator for you to create a genuine random number from any two numbers. For example, to generate an random number that is in the range of one to 10 (including 10, you must enter 1 first in the input field, and 10 in the next field, and finally click "Get Random Number". The randomizer selects one of the numbers from 1 to 10 random. To generate an random number between 1 and 100, utilize the same method for 100, but it is within the 2nd field on the randomizer. In order to simulation of rolling a dice the interval of numbers should be 1-6 in the case of ...
Read more

Mahakali Chalisa PDF in Hindi

Image
Mahakali Chalisa PDF in Hindi Mahakali Chalisa Lyrics Mahakali Chalisa Hindi PDF for free using the direct download link given at the bottom of this article. दोहा जय जय सीताराम के मध्यवासिनी अम्ब, देहु दर्श जगदम्ब अब करहु न मातु विलम्ब ॥ जय तारा जय कालिका जय दश विद्या वृन्द, काली चालीसा रचत एक सिद्धि कवि हिन्द ॥ प्रातः काल उठ जो पढ़े दुपहरिया या शाम, दुःख दरिद्रता दूर हों सिद्धि होय सब काम ॥ चौपाई जय काली कंकाल मालिनी, जय मंगला महाकपालिनी ॥ रक्तबीज वधकारिणी माता, सदा भक्तन की सुखदाता ॥ शिरो मालिका भूषित अंगे, जय काली जय मद्य मतंगे ॥ हर हृदयारविन्द सुविलासिनी, जय जगदम्बा सकल दुःख नाशिनी ॥ ४ ॥ ह्रीं काली श्रीं महाकाराली, क्रीं कल्याणी दक्षिणाकाली ॥ जय कलावती जय विद्यावति, जय तारासुन्दरी महामति ॥ देहु सुबुद्धि हरहु सब संकट, होहु भक्त के आगे परगट ॥ जय ॐ कारे जय हुंकारे, महाशक्ति जय अपरम्पारे ॥ ८ ॥ कमला कलियुग दर्प विनाशिनी , सदा भक्तजन की भयनाशिनी ॥ अब जगदम्ब न देर लगावहु, दुख दरिद्रता मोर हटावहु ॥ जयति कराल कालिका माता, काल...
Read more