Next Gen Data Learning – Amplify Your Skills

Blog Home

Blog

Power BI CALCULATE: Key To Advanced DAX Formulas

by | DAX, Power BI

Now we are going to dive into the CALCULATE function in Power BI.

We have already used the function many times but this is where I want to discuss it in detail.

Getting The Sales Last Year With CALCULATE

The CALCULATE function allows you to change the context of a calculation within a measure wherein the context is coming from the environment where your calculation is being done. You may watch the full video of this tutorial at the bottom of this blog.

The ability to change the context within a measure is very important in DAX.

We will start with an example by creating a measure called Sales LY which uses the CALCULATE function to compare the sales on the current date to the sales on the same date last year.

Creating Sales LY - Power BI CALCULATE

Within the CALCULATE function, we will put in our Total Sales since the first parameter of CALCULATE can take any kind of expression.

Then, I will use a time intelligence function called SAMEPERIODLASTYEAR. Time intelligence functions are functions used for time-related calculations.

Since we are putting SAMEPERIODLASTYEAR inside CALCULATE, we will be adjusting our current context by one year through our Date column.

Inserting CALCULATE Parameters - Power BI CALCULATE

Upon dragging in the new measure, you will see that the Total Sales and Sales LY columns have different results.

Total Sales vs Sales LY - Power BI CALCULATE

The reason behind this is that the values inside Sales LY are taken from the Total Sales exactly one year before.

To show an example, let us have a look at the Sales LY value of the date 28/02/2020.

Showing the date 28/02/2020 - Power BI CALCULATE

Since it has a value of $10,440, looking back at the Total Sales of the date 28/02/2019 should yield the exact same result.

Showing values of 28/02/2019 - Power BI CALCULATE

It is recognizing 28/02/2020 as the present date, but it changes the context to the same date in 2019 through the Sales LY column.

This is why CALCULATE is such an important function in Power BI. It allows you to perform calculations in many different ways, like when you need to find the difference between the Total Sales and Sales LY.

Changing The Context By Quarter

I can also change the context of the Date column so that I could look at quarterly values instead of daily values.

I will first drag in the QuarterInCalendar column which can be found inside the Dates table.

Showing values of QuarterInCalendar - Power BI CALCULATE

The first thing that you will notice here is that the dates are not sorted correctly. To fix this, I will sort the column called QuarterInYear.

I will go to the Data area and highlight the QuarterInCalendar column.

Highlighting QuarterInCalendar

Then, I can select the QuarterInYear option under Sort by column.

Sorting by column with QuarterInYear

Once we go back to the previous screen, you can now see that we already have the quarters in the right order.

Result of sorting QuarterInCalendar column

We are doing the exact same thing earlier, but the values are accumulated in a different granularity since we are looking at quarters instead of days.

For instance with Q2 2020, we have a Total Sales value of $2,839,495. But when we look at the Sales LY value, the CALCULATE function changes the context back one year, which has the Q2 2019 Total Sales value of $3,665,971.

Q2 2019 vs Q2 2020

The CALCULATE function enables you to work in different ways using a similar insight without having to rewrite formulas or calculations.

Total Sales Of Certain Location Using CALCULATE

I also want to show you another way how you can adjust the context inside CALCULATE.

We previously changed the contexts of the calculations completely, but now I want to show you an example where we would instead filter the context slightly.

What if I wanted to calculate all of my sales in a certain location, specifically North Carolina?

To do this, I can create a new measure called Sales in NC, use CALCULATE with Total Sales as the expression, and then use the State Code in the Locations table as the filter and set it equal to “NC”.

Creating Sales in NC measure

Once we drag in our new measure, we can see a breakdown of the Total Sales in just a particular region based on the context of Product Name.

Showing values of Sales in NC

Compared to Sales LY where the context was changed completely, the context in Sales in NC was simply adjusted.

This is why CALCULATE will become your most used function since there are many ways that you can adjust the context.

If you look at any of the advanced content from Enterprise DNA, you will see that CALCULATE is used in almost everything since formulas can be written very efficiently using it.

You might have already seen very long formulas written in Microsoft Excel which span two to three lines. In Power BI, you can have a whole range of different calculations by simply adjusting the context.

To show another example, I can drag in the Sales in NC measure along with the QuarterInCalendar measure.

Sales in NC with QuarterInCalendar as the initial context

I did not have to make changes in the formula, but I added to the context compared to our previous example where we adjusted it.

Since we already have our quarterly sales, adding in Sales in NC gave us quarterly sales particular to North Carolina.

There is that slight difference compared to completely changing the context like in Sales LY where we used a time intelligence function to manipulate the date.

The Sales in NC measure is just a filter on location which is not related to the initial context of QuarterInCalendar which is date.

***** Related Links *****
Calculating Dynamic Percentage Of Total Change Using Power BI Time Intelligence
Calculating A Cumulative Run Rate Using DAX In Power BI
How To Calculate Difference In Days Between Purchases Using DAX In Power BI

Conclusion

That was just an introduction to the CALCULATE function in Power BI on how you could use it to change, adjust, or add to the context of your calculations.

I want you to think about how you can apply this on something that you are working on. If you can utilize this function, you can quickly do formula combinations and techniques to calculate more things.

All the best,

Sam

[youtube https://www.youtube.com/watch?v=q8VF2CeMZ5o&w=784&h=441]

Related Posts