Filter

Hi swalke ,

This is a pretty classic challenge in Power BI, and I’ve run into it a few times myself! The main thing is to make sure your matrix always shows all quarters, with past quarters ignoring the date slicer and only the current quarter reacting dynamically.

Here’s what’s worked for me:

1. Make sure your Calendar table is complete. Your Calendar table should cover all the dates/quarters you want to see, even if there’s no sales data. Set it up as a proper Date table and relate it to your sales table.

2. Use "Show items with no data" in your matrix: Right-click your Quarter field in the matrix and select “Show items with no data.” This ensures that all quarters appear, even those with zero sales.

3. Use a DAX measure like this:

Sales Measure =
VAR CurrentQuarter =
    CALCULATE(MAX('Calendar'[Quarter]), ALLSELECTED('Calendar'))
VAR SelectedQuarter =
    SELECTEDVALUE('Calendar'[Quarter])
VAR SelectedYear =
    SELECTEDVALUE('Calendar'[Year])
RETURN
IF(
    SelectedQuarter = CurrentQuarter,
    // For current quarter: respect the slicer
    CALCULATE(SUM('Sales'[Amount])),
    // For previous quarters: ignore date slicer
    CALCULATE(
        SUM('Sales'[Amount]),
        ALL('Calendar'),
        'Calendar'[Quarter] = SelectedQuarter,
        'Calendar'[Year] = SelectedYear
    )
)

Adjust [Quarter] and [Year] as needed for your schema. If you want to use a custom “Current Quarter” (e.g. picked by a slicer), you can tweak the logic above.

4. Place Quarter and Category from your Calendar table in the matrix (not the sales table).