Hi @frankhofmans, Hope you are doing good !
To achieve your goals in Power BI, you can leverage the quarter offset in your date table combined with DAX measures to handle the filtering and calculations for the preceding quarters. Here’s how to set up your quarterly report and create the specific formulas you need:
1. Setting Up the Date Table
Ensure that your date table has:
A Quarter Offset column (already present in your case).
A Quarter-Year column (e.g., "Q1 2024"), which will be used in the slicer.
2. Creating a Slicer for Quarter-Year
Add the Quarter-Year column from your date table to a slicer.
Ensure the slicer is set to single select mode.
3. Filtering the Visuals for the Selected and Preceding Quarters
To display the selected quarter and the four preceding quarters:
Create a new DAX measure for filtering the visuals:
Show Selected and Prior Quarters =
VAR SelectedQuarterOffset = MAX('Date'[Quarter Offset])
RETURN
IF(
'Date'[Quarter Offset] <= SelectedQuarterOffset &&
'Date'[Quarter Offset] >= SelectedQuarterOffset - 4,
1,
0
)
Apply this measure as a filter to your charts:
Drag the Show Selected and Prior Quarters measure to the Filters pane for the visual.
Set the filter condition to is 1.
Creating a Measure for the Quarter Two Periods Before
To calculate the number of results for the quarter that is two quarters prior to the selected quarter:
Create the following measure:
Results for -4 Quarter =
VAR SelectedQuarterOffset = MAX('Date'[Quarter Offset])
VAR TargetQuarterOffset = SelectedQuarterOffset - 2
RETURN
CALCULATE(
SUM('YourTable'[YourValueColumn]),
'Date'[Quarter Offset] = TargetQuarterOffset
)
