Filter

[swalke]

Hello,
Need to create a measure to show sales with below conditions.
1) A matrix visual to show sales by quarter and category (2 category)
so all previous quarters sales (ignoring the date slicer) + current quarter sales (between the selected dates) 2) Sales should be quarterly basis (Q1-25, Q2-25)
3) There is a date slicer (from calendar table) with between type. This date slicer will only show dates for current quarter. I have a column to show current quarter.
In the matrix i want to show sales for all the quarter avoiding the date slicer but for current quarter the measure should dynamically calculates sales between the selected date.

[v-hjannapu · Accepted solution]

Hi @swalke,

Thank you  for reaching out to the Microsoft fabric community forum.

As per your requirement, the final solution shows sales for all previous quarters regardless of the slicer, and only current quarter sales filtered by selected dates using a disconnected quarter table and a smart DAX measure. The matrix now accurately displays quarters in rows and categories in columns with correct values.

I tested it with my sample data, and it worked fine. Please find the attached screenshot and Pbix for your reference.

Hope this helps if you have any queries we are  happy to assist you further.
Best Regards,
Harshitha.

[v-hjannapu]

Hi @swalke,
I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.

[v-hjannapu]

Hello @swalke,
Thank you for posting your query in Microsoft Fabric Community Forum. Also, thanks to @rohit1991, @danextian,   @burakkaragoz  for those inputs on this thread. 

I wanted to check if you had the opportunity to review the information provided earlier Please feel free to contact us if you have any further questions.

Regards,
Harshitha.

[v-hjannapu]

Hi @swalke,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.

Regards,
Harshitha.

[rohit1991]

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).

[swalke]

Hi,
Thank you for your quick response,
unfortuantely the measure provided by you is not fulfilling my need. The measure is not able to show previous quarters in the measure.

[danextian]

Hi @swalke 

Please share a sample dataset in a usable format (not a screenshot), along with the expected output based on that data and your reasoning behind it. You may also upload an Excel file or a sanitized PBIX file via a cloud link. Preparing this may take some time, but so does arriving coming up with a solution.

[burakkaragoz]

Hi @swalke ,

This is a tricky one but I've got you covered. You need a measure that treats past quarters and current quarter differently.

Here's the solution:

Sales Measure = 
VAR CurrentQuarter = 
    CALCULATE(
        MAX('Calendar'[Quarter]),
        REMOVEFILTERS()
    )
VAR SelectedQuarter = MAX('Calendar'[Quarter])
VAR IsCurrentQuarter = SelectedQuarter = CurrentQuarter

RETURN
IF(
    IsCurrentQuarter,
    -- Current quarter: respect date slicer
    SUM('Sales'[Amount]),
    -- Previous quarters: ignore date slicer
    CALCULATE(
        SUM('Sales'[Amount]),
        REMOVEFILTERS('Calendar'[Date])
    )
)

How this works:

• For previous quarters (Q1-25, Q2-25, etc.): Uses REMOVEFILTERS to ignore the date slicer completely
• For current quarter: Respects the date range selected in your slicer
• The matrix will show all quarters but only the current one will be affected by date selections

Alternative if you have a current quarter flag:

Sales Measure = 
IF(
    MAX('Table'[IsCurrentQuarter]) = 1,
    SUM('Sales'[Amount]),
    CALCULATE(
        SUM('Sales'[Amount]),
        REMOVEFILTERS('Calendar'[Date])
    )
)

Put this measure in your matrix with Quarter and Category, and it'll behave exactly like you described. Past quarters stay fixed, current quarter responds to your date slicer.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

This response was assisted by AI for translation and formatting purposes.

[swalke]

Hi,
Thank you for your quick response,
unfortuantely the measure provided by you is not fulfilling my need. The measure is not able to show previous quarters in the measure.

[swalke]

Thank you for responding,
I tried both version of the DAX provided by you but it is not fullfilling my need. The matrix visual is showing sales for only current quarter and previous quarter data is not visible in the matrix.