Power BI DAX: CALCULATE, SUM, SAMEPERIODLASTYEAR
CALCULATE ( SUM ( Sales[Sales] ), SAMEPERIODLASTYEAR ( dimDate[Date] ) )
Brief explanation
The CALCULATE(A,B) 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:
Filter Context Modification (B): The filter context specified by B is applied first. This could be a filter condition, a time intelligence function like SAMEPERIODLASTYEAR, or any other context modifier.
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'[SalesAmount]), SAMEPERIODLASTYEAR('Date'[Date])), it performs the following:
SAMEPERIODLASTYEAR('Date'[Date]): Adjusts the date context to the same period in the previous year.
SUM('Sales'[SalesAmount]): Calculates the total sales amount.
CALCULATE(): Applies the date adjustment from SAMEPERIODLASTYEAR() to the SUM() calculation, giving you the total sales for the same period last year.
This combination allows you to compare current sales with sales from the same period in the previous year, providing valuable insights into performance trends.
Illustration
Let's say you have the following sales data:
| Date | Sales |
| 2023-01-01 | 100 |
| 2023-01-02 | 150 |
| 2023-01-03 | 200 |
| 2024-01-01 | 120 |
| 2024-01-02 | 180 |
| 2024-01-03 | 220 |
And your dimDate table has the following dates:
| Date |
| 2023-01-01 |
| 2023-01-02 |
| 2023-01-03 |
| 2024-01-01 |
| 2024-01-02 |
| 2024-01-03 |
If you use the DAX expression CALCULATE(SUM(Sales[Sales]), SAMEPERIODLASTYEAR(dimDate[Date])) in a report for January 2024, it will calculate the sum of sales for the same period in 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:
| Date | Sales (2024) | Sales (Same Period Last Year) |
| 2024-01-01 | 120 | 100 |
| 2024-01-02 | 180 | 150 |
| 2024-01-03 | 220 | 200 |
This allows you to compare the sales performance of the current period with the same period in the previous year.