In Power BI, DAX provides the solution for calculating differences between two non-adjacent periods in functions such as CALCULATE, DATEADD, and FILTER. A measure can be designed thus to make it context-wide and dynamic indeed.
Method: CALCULATE, DATEADD, and FILTER
Create the Measure:
DAX to define a measure, wherein the measure calculates sales over two periods; finally calculates the difference between those measures:
Sales Difference =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
ALL(Sales),
Sales[Date] IN {DATE(2024, 1, 1), DATE(2024, 3, 31)}
)
)
-
CALCULATE(
SUM(Sales[Amount]),
FILTER(
ALL(Sales),
Sales[Date] IN {DATE(2023, 10, 1), DATE(2023, 12, 31)}
)
)
Explanation of the Measure:
-
CALCULATE: Applies the aggregation based on specific conditions.
-
FILTER: Allows selection of specific dates by using IN for non-contiguous periods.
-
ALL(Sales): Removes existing filters on Sales table to ensure accurate calculations.
-
This measure dynamically adjusts based on filters applied in visuals.
Alternative Approach: Using Variables for Clarity
For better readability and flexibility, use DAX variables:
Sales Difference (Dynamic) =
VAR Period1Sales =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
ALL(Sales),
Sales[Date] IN {DATE(2024, 1, 1), DATE(2024, 3, 31)}
)
)
VAR Period2Sales =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
ALL(Sales),
Sales[Date] IN {DATE(2023, 10, 1), DATE(2023, 12, 31)}
)
)
RETURN
Period1Sales - Period2Sales
Dynamic Filtering with Slicers (Advanced Option):
If users need to select periods dynamically using slicers, create two measures for each period using SELECTEDVALUE or MAX to capture slicer selections. Then calculate the difference.