Power BI DAX: CALCULATE, SUM, PREVIOUSYEAR

CALCULATE ( SUM ( Sales[Sales] ), PREVIOUSYEAR ( dimDate[Date] ) )

Brief explanation

The CALCULATE() function in DAX is used to modify the filter context of an expression. It allows you to apply specific filters to your data before performing calculations, making it a powerful tool for dynamic and context-sensitive analysis.

How it works:

1. Filter Context Modification (B): The filter context specified by B is applied first. This could be a filter condition, a time intelligence function like PREVIOUSYEAR, or any other context modifier.

2. Expression Evaluation (A): Once the filter context is modified by B, the expression A is then evaluated within this new context.

Example

When you use CALCULATE(SUM(Sales[Sales]), PREVIOUSYEAR(dimDate[Date])), it performs the following:

1. SUM(Sales[Sales]) • This part of the expression calculates the total sales amount from the Sales table.

2. PREVIOUSYEAR(dimDate[Date]) • This function shifts the dates in the current context back by one year, returning a table of dates from the previous year.

3. CALCULATE • The CALCULATE function modifies the filter context of the SUM calculation to include only the dates from the previous year, as specified by PREVIOUSYEAR.

Illustration

Let's say you have the following sales data:

And your dimDate table has the following dates:

If you use the DAX expression CALCULATE(SUM(Sales[Sales]), PREVIOUSYEAR(dimDate[Date])) in a report for January 2024, it will calculate the sum of sales for January 2023.

So, for January 2024, the calculation would be:

• 2024-01-01: Sum of sales on 2023-01-01 = 100

• 2024-01-02: Sum of sales on 2023-01-02 = 150

• 2024-01-03: Sum of sales on 2023-01-03 = 200

The result for each date in January 2024 would be:

This allows you to compare the sales performance of the current period with the same period in the previous year.