Filter Out Future Quarters in Matrix Visual

[smmcqueen]

I have report with a Calendar table and an Expenses table. I used the Calendar table to create Year-to-Date, Quarter-to-Date, and Year-over-Year change measures. The Calendar table contains future dates so that I don't have to keep updating it as we add new Expense data. We are currently in Q2 of our fiscal year and the measures are working as intended for past dates. However, quarters 3 and 4 are showing up in the Matrix visual, in spite not having expenses since they are a future date. When I try to filter all FY25 dates from the visual, the matrix breaks because the measures are reliant on the filtered date column (see Image 1 below). When I try to filter the FY25 dates in the Data tab or Power Query, the Q3 and Q4 rows are still there (see Image 2). Any advice for getting rid of Q3 and Q4 (preferably in a way that does not require manual update whenever data for those quarters is input)?

Image 1: Filter Date on Visual Error

Image 2: Current View of the Matrix

[v-xiaocliu-msft]

Hi @smmcqueen ,

If the problem persists, please consider sharing sample data and formulas.

Best Regards,

Wearsky

[smmcqueen · Accepted solution]

I found the solution. If you right-click the row, there is an option to exclude. 

[SamWiseOwl]

Hi @smmcqueen 

Have you tried modifying your measures to return blank for future dates?

Hidden Expense YTD =

IF(
 MIN(datetable[date]) > Today(), BLANK(), [Your calculation])

Do this for all the measures, any rows that are completely Blank will be hidden by Power BI.

Alternatively you might be able to create a measure and apply a filter in the Filter pane, drag the measure in "Filters on this visual" then filter to Y.

Hide rows = IF(
 MIN(datetable[date]) < Today(), "Y")

Hope this helps!

[smmcqueen]

Thank you for the prompt response! I will try that and let you know if it works. Thank you!

[SamWiseOwl]

Alternatively you might be able to create a measure and apply a filter in the Filter pane, drag the measure in "Filters on this visual" then filter to Y.

Hide rows = IF(
 MIN(datetable[date]) < Today(), "Y")

This would only need to be done once vs once for each measure.