Power BI DAX: CALCULATE, PARALLELPERIOD
Sales Previous Year =
CALCULATE ( [Total Sales], PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) )
The CALCULATE() function in DAX is essential for modifying the context of a calculation, allowing you to apply specific filters or conditions. When integrating PARALLELPERIOD(), CALCULATE() shifts the date context to a different period, such as the same period in the previous year. This combination enables you to compute measures like "Sales Previous Year" by adjusting the date range and recalculating the total sales within that new context. Essentially, CALCULATE() redefines the data scope, while PARALLELPERIOD() specifies the time shift, together providing a powerful way to analyze time-based data.
Scenario
You are a sales manager at a retail company, and you need to create a report that shows the total sales for the current year and the previous year. This will help you understand how your sales are performing year-over-year and identify any significant changes.
Example
Assume you have a Sales table with the following columns:
• OrderDate: The date when the order was placed.
• SalesAmount: The total sales amount for the order.
You want to calculate the total sales for the previous year to compare it with the current year's sales.
To achieve this, you can use the following DAX expression:
Sales Previous Year =
CALCULATE ( [Total Sales], PARALLELPERIOD ( 'Sales'[OrderDate], -1, YEAR ) )
Explanation
• CALCULATE: This function modifies the context of the calculation by applying the specified filters.
• PARALLELPERIOD: This function shifts the date context by the specified interval, which in this case is one year back.
Example Data
Let's say you have the following data in your Sales table:
OrderDate | SalesAmount |
2023-01-15 | 1000 |
2023-02-20 | 1500 |
2022-01-10 | 1200 |
2022-02-25 | 1300 |
Using the DAX expression, you can calculate the total sales for the previous year (2022) and compare it with the current year's sales (2023).
Result
When you apply this expression in your report, you will get the total sales for the previous year, allowing you to compare it with the current year's sales and analyze the year-over-year performance.