Forum Discussion
Matrix Row Filtering Alters Rolling DATESINPERIOD Measures
Has anyone come across a limitation with rolling measures in a matrix?
Context
Data is structured as Tests grouped into Journeys. Each Journey has a monthly volume (number of tests in that month), and there is also a rolling 3‑month percentage calculated as total harms ÷ total volume using DATESINPERIOD.
Issue
The rolling 3‑month measure is correct when all Journeys are included. The problem arises when a Journey has no activity in the current month (i.e. the monthly volume is BLANK). If those Journeys are excluded - whether via visual filters or conditional display logic - the rolling 3‑month percentage changes because the filter context is altered. If they are not excluded, the calculation remains correct, but the matrix shows Journeys with no current‑month activity.
I’ve tried both measure‑level blanking and visual‑level filtering, but in each case I hit the same trade‑off: excluding rows affects the calculation, while preserving the calculation requires keeping rows with blank current‑month data.
Is there a known pattern in Power BI for controlling row visibility independently of calculation context in this scenario, or is this simply a limitation of how table/matrix visuals propagate filter context?
In short: I can either have blank rows with a correct rolling 3‑month percentage, or filter out the blanks and cause the rolling percentage to change, but not both.
P.S. I don't have the kind of account that can link a demo PBIX.
Hi AC23VM ,
Thanks for your patience while we looked into this further.
We tested a workaround using conditional formatting to suppress the value columns for Journeys with no current-month activity. While this hides the values themselves, the Journey name still remains visible in the row header and the blank row continues to take up space in the matrix. That was the closest behavior we could achieve within a single matrix visual.
So based on our testing, your understanding is correct. In a single matrix visual, it isn't currently possible to fully achieve all three together: keeping historical Journey data for the rolling window, hiding Journeys with no current-month activity, and preserving correct per-Journey rolling values.
One possible approach is to split the requirement across two visuals. For example, you could use one visual for the overall rolling % calculation using ALLSELECTED or REMOVEFILTERS, and a separate matrix filtered to active Journeys only for the row-level breakdown.
In most cases, filtering inactive Journeys at the row level should not affect other Journey rows if each rolling measure is scoped correctly, though that would still depend on the exact DAX logic being used in the model.
From what we could determine, this is current matrix visual behavior in Power BI, since row visibility and filter context are tightly connected by design.
Hope this helps. Please reach out for further assistance.
Thank you.
5 Replies
Hi AC23VM,
You will likely need to use the KEEPFILTERS DAX function so that previous months are still included in the measure context.
If you could share the pbix file (google drive or similar) or share sample data in a useable form (not a screenshot) and the expected output, we can likely help make a new file for you.
Add Has Current Month Activity as a Filters on this visual filter set to is 1.
Has Current Month Activity = IF ( CALCULATE ( SUM ( Tests[Volume] ), ALLSELECTED ( 'Date'[Date] ), MONTH ( MAX ( 'Date'[Date] ) ) = MONTH ( TODAY () ), YEAR ( MAX ( 'Date'[Date] ) ) = YEAR ( TODAY () ) ) > 0, 1, BLANK () )Hi AC23VM ,
Thanks for posting in Microsoft Fabric Community.
What you're seeing is expected behavior in matrix visuals because the same filter context is used both for row visibility and for evaluating the rolling measure. So when a Journey with no current-month activity is filtered out, its historical rows are also removed from the rolling 3-month calculation, which causes the percentage to change.
The usual approach in this scenario is to use a separate measure to control row visibility while writing the rolling measure to override the Journey-level filter context inside CALCULATE using ALLSELECTED, ALL, or REMOVEFILTERS depending on the requirement.
Assuming [Harms] and [Volume] are already defined as measures in your model, a simplified pattern would look like:
Rolling 3M % = CALCULATE ( DIVIDE ( [Harms], [Volume] ), DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH ), ALLSELECTED ( Journey[JourneyID] ) )ALLSELECTED on the Journey column removes the row-level filter the matrix applies per Journey while still respecting any slicers or page-level filters the user has set, which is what keeps the rolling calculation stable when rows are hidden.
If the rolling % is intended to be calculated across all Journeys, this pattern should help. If each Journey row is supposed to show only its own rolling value, then filtering one Journey should not affect another. if it does, the issue is likely in the current measure logic rather than the visibility filter.
For reference, the ALLSELECTED behavior is covered in the official DAX documentation here: ALLSELECTED – DAX Guide
A similar thread where a rolling measure was affected by date range filtering and resolved using similar approach: Solved: DATESINPERIOD All or Remove Filters - Microsoft Fabric Community
If possible, please share the current rolling measure definition or a small sample model structure so we can suggest a more precise adjustment. You can also upload your pbix file to a cloud storage location like OneDrive or GoogleDrive and share the downloadable link here.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community.
Hope this helps. Please reach out for further assistance.
Thank you.
Thanks for the detailed reply - I appreciate you taking the time to explain the behaviour, and I agree that what’s happening is expected given how matrix visuals apply filter context. Where I’m struggling is that, in this particular scenario, the usual ALL / ALLSELECTED pattern doesn’t seem to get me past the trade‑off.
To clarify my setup a bit more, my rolling calculation already has both the numerator and denominator wrapped in the same DATESINPERIOD window, for example:
- Harms (3M) = CALCULATE( … , DATESINPERIOD(…))
- Volume (3M) = CALCULATE( … , DATESINPERIOD(…))
- Rolling % = DIVIDE( [Harms (3M)], [Volume (3M)] )
With this approach, the 3‑month rolling result is mathematically correct as long as all Journeys remain in the matrix, including those with no current‑month activity.
The difficulty is entirely around row visibility:
- If I keep Journeys with BLANK current‑month volume, the rolling % is correct, but the matrix includes rows that aren’t very meaningful to the user.
- If I remove those rows (via visual filters or measure‑level blanking), the Journey filter context changes, and those Journeys also drop out of the historical 3‑month window - which causes the category‑level rolling % to change.
I did try the suggested approach of removing the Journey row context using ALLSELECTED / REMOVEFILTERS. That does stabilise the rolling calculation, but it introduces a different issue for me: the same category‑level rolling value ends up being repeated on each Journey row, which no longer reflects how each Journey contributes within the rolling period:
So I feel a bit stuck between these competing requirements inside a matrix:- Preserve Journey history for the rolling window
- Hide Journeys with no current‑month activity
- Avoid repeating a single category‑level value on every Journey row
At the moment, it looks like row visibility and calculation context can’t really be decoupled in a table/matrix visual - hiding rows inevitably feeds back into filter context for the measure.
That’s mainly what I’m trying to sanity‑check: whether anyone has found a reliable workaround for controlling row visibility independently of a rolling DATESINPERIOD calculation, or whether this is simply a known limitation of how table/matrix visuals behave. I suspect it may be the latter, but I wanted to check before redesigning the visual or model.
Hi AC23VM ,
Thanks for your patience while we looked into this further.
We tested a workaround using conditional formatting to suppress the value columns for Journeys with no current-month activity. While this hides the values themselves, the Journey name still remains visible in the row header and the blank row continues to take up space in the matrix. That was the closest behavior we could achieve within a single matrix visual.
So based on our testing, your understanding is correct. In a single matrix visual, it isn't currently possible to fully achieve all three together: keeping historical Journey data for the rolling window, hiding Journeys with no current-month activity, and preserving correct per-Journey rolling values.
One possible approach is to split the requirement across two visuals. For example, you could use one visual for the overall rolling % calculation using ALLSELECTED or REMOVEFILTERS, and a separate matrix filtered to active Journeys only for the row-level breakdown.
In most cases, filtering inactive Journeys at the row level should not affect other Journey rows if each rolling measure is scoped correctly, though that would still depend on the exact DAX logic being used in the model.
From what we could determine, this is current matrix visual behavior in Power BI, since row visibility and filter context are tightly connected by design.
Hope this helps. Please reach out for further assistance.
Thank you.
