In this tutorial, I want to show you what the CALCULATE function can do through a few examples.
CALCULATE is one of the most frequently used functions in Power BI. It dictates the context being applied to any measure and removes any existing filters in place.
Using CALCULATE Function For Total Sales
To truly understand the impact that CALCULATE has, it’s important to remember that any result within Power BI is computed row per row. It all depends on the context given.
Let’s take a look at this sample data. For this row, the measure gives us a result of 108,466.30 when the CALCULATE function was used. This CALCULATE column basically shows the numbers from the same date last year through the use of the SAMEPERIODLASTYEAR function.
That result is based on the initial context applied, which is the date. For this specific row, the date given is August 14, 2020.
That date is also the initial context for the Total Sales measure, which gives us 220,148.60 in that row based on the present time.
But what if within this particular context, I also want to show what we made from a sales perspective but from a different time? This is where CALCULATE becomes even more useful.
Using The CALCULATE Function With DATEADD
CALCULATE is currently allowing me to compare the Total Sales for the current time period and the sales from last year on the same day. But this comparison doesn’t necessarily have to be done on an annual basis. We can do day-to-day comparison, and even monthly and quarterly calculations as well.
Before we do that, let’s take a look at the original measure.
Using CALCULATE, I referenced the Total Sales and used the function SAMEPERIODLASTYEAR. This function allows me to jump exactly one year before the current date.
Of course, that sets a restriction on my calculation because it won’t allow me to go beyond the one-year period. So I’ll create a new measure that will let me to do that.
I’m going to start the measure the same way the original measure was made. I’m going to use CALCULATE and reference Total Sales.
Then, I’m going to use the time intelligence function DATEADD and set it to go back for just one quarter.
Once I add that into my table, you’ll see that I now have data that shows the numbers from one quarter ago.
That’s the advantage of using DATEADD in your measures. It allows you to set any timeframe, as opposed to SAMEPERIODLASTYEAR, which only counts exactly one year prior to the current date.
This time, let me change that measure and set the time back to only one day, instead of one quarter.
As expected, the result I’m getting shows very little difference from the original figure, knowing that only one day has passed.
Using CALCULATE With The FILTER Function
You can also use CALCULATE with the FILTER function, which allows us to add even more depth to our analysis.
For this example, let’s see what’s going to happen if we FILTER through ALL the Dates.
Next, I’m going to set it to MAX date minus 7, which means one week from the current date.
This gives us a rolling average that looks back at the numbers from a week before, but this time, we’re using FILTER to go back instead of DATEADD.
Again, it all goes back to the context applied. We’re starting off with the initial context, which looks at the current date, then we’re adding more to that context using the CALCULATE function so that we can dictate a new timeframe to look into.
Conclusion
CALCULATE can definitely give you more range to your reports and allows you to make comparisons on your numbers across different timelines. This is something that you can use on elements other than Total Sales, too.
Hopefully you can start applying this function to your own work soon. Feel free to explore other contexts you can add to your measures using CALCULATE.
All the best,
Sam
***** Related Links *****
A New Look At CALCULATE – Power BI
Power BI Holiday Calendar – Calculate Date W/DAX
Power BI CALCULATE: Key To Advanced DAX Formulas
***** Related Course Modules *****
Time Intelligence Calculations
Mastering DAX Calculations
Unique Analytical Scenarios
***** Related Support Forum Posts *****
CALCULATE Function With Several Filters
Calculate Function In A Measure Breaks Cross-Table Filtering?
How To Utilise A Boolean Expression Within The CALCULATE Function
For more CALCULATE function queries to review, see here…
