Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Dynamically Calculating The Previous Highest Result Using DAX Functions

by | 9:00 am EDT | June 06, 2020 | Power BI

DAX functions greatly expand dynamic calculations. They also broaden the other important capabilities in Power BI.  In this tutorial, we’ll discuss how you can dynamically calculate the previous highest result in Power BI. We’ll also be learning the right combination of formula and techniques for this particular calculation. You may watch the full video of this tutorial at the bottom of this blog.

By doing a dynamic calculation, we can run the calculation of a selected data set, while their previous highest result continuously updates and is being displayed visually.

I’ll be showing you how you can actually do this. The formula for this does not need to be too complex at all. It just requires a really good understanding of iterating functions to make it work.

## Getting Data Into A Table

Let’s duplicate our current visualization here and we’ll have a look at it as a table.

Always make sure you get your data into a table. This allows you to actually have a look at the results.

Before anything else, let’s change the format of the Date column into this format that I usually prefer.

## Combination Of DAX Functions For Previous High Result Calculation

Here is the combination of DAX functions that we will use to calculate the previous highest result in your Power BI reports.

Take note that it is essential to get inside tables to fully understand what data you’re looking at.

Basically, we need to look back across every previous day. Then, identify what the maximum revenue date on any day was. Moreover, as we go down the list, the dates that we need to work through are going to be larger and larger.

This is because we’ve got to historically look back over every previous day for every single result in our table. That is exactly what I have done in this formula.

## Using The MAXX DAX Function

In this formula, I used an iterating function called MAXX.

This allows you to create the previously mentioned iteration. This iteration is essential for calculating the previous highest result.

All we need to do is to place the revised virtual table for every single result that we need to iterate through using this logic.

Now, let’s try to work through an example for this calculation.

## Using FILTER And ALLSELECTED DAX Functions

Let’s have a look at 6th of January, 2018 which will serve as our current date.

This FILTER function within the formula will look back through every single date in the currently applied context or filter. This is made possible with the help of the ALLSELECTED function.

The ALLSELECTED function only looks at dates within the current context (in this case, 2018).

If we used the ALL function instead of ALLSELECTED, it would look back through every single date from the beginning of time.

## Evaluating Through Every Single Dates

After looking through the dates in the current context, it’s going to iterate through every single day within your table. Then, it’s going to filter out the days that are less than or equal to the current date in the particular context.

The current date is calculated using the MAX function.

In this context, the current date is the 6th of January. Therefore, if the date is less than or equal to the current date, that will equate to TRUE.

Furthermore, that is also going to be the virtual table of dates that we will have within this iterating function.

Additionally, we will iterate through only those particular dates. Then, we will identify the maximum revenue day (Total Revenue).

Consequently, that’s what it does to every single row in this table. Every single calculation is done exactly the same.

Obviously, this virtual table gets bigger and bigger as you move down the list. And when you set it up exactly like this, it becomes completely dynamic.

By doing that, you can select anything from the filters. You can even change the date and time frame as well.

In addition, you’ll see that the Previous High visualization continuously reevaluates to calculate the highest value on an ongoing basis.

In other words, it’s like a live MAX value. If we add more and more filters, we’d still get updated results.

This formula is obviously not too complex. However, it can produce and add a few insights for your reports. Once you get the correct calculations into the table, you can finally change it into a Line and stacked column chart.

## Conclusion

To sum up, the key for this particular insight is to make sure you understand iterating functions. It will also allow you to place virtual tables inside them as well.

This highlights the outstanding capabilities of Power BI in terms of creating simple formulas, and the dynamic results that you can easily get.

This is quite a unique calculation which holds a lot of value. It can also add a little bit of color to your insights.

Good luck in trying to implement this into your own models.

For more information on how you can combine important DAX functions to find complex and valuable data insights in Power BI, you can check out this module at Enterprise DNA Online.

Sam

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...