Forum Discussion
Need help with Two Date Filter Contexts on Same Page
Hi peterpure,
Thank you for your thoughtful follow-up and for considering the suggestions from Community members.
When you place Month Year on the X-axis, it takes filter context directly from your slicer, which limits the visual to only the selected month. This setup conflicts with the goal of showing multiple periods like MTD, QTD, or YTD for Month-on-Month comparisons.
To address this, you can use a disconnected Month-Year table. This lets users make selections without filtering your Fact table directly. In these cases, create measures that read the selected period (MTD/QTD/YTD) from the disconnected slicer using SELECTEDVALUE, and apply custom filtering in your DAX measures with CALCULATE and REMOVEFILTERS or ALL. These functions help override slicer filters, enabling accurate period-over-period analysis across the full time axis. This way, you can display Month-on-Month growth metrics while maintaining the chosen aggregation level.
Regarding performance, using SUMX directly on a column can be more efficient for simple aggregations, but when you need to manipulate context, CALCULATE is necessary. For better performance, use variables (VAR) for intermediate calculations, avoid unnecessary context transitions, and use tools like Performance Analyzer in Power BI Desktop to test and optimize your model.
Thank you.
Community Championpeterpure The short answer is that you need to use ALL to remove the Month context and replace it with different context.
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
Also, this is for YoY but MoM is effectively the same: Better Year Over Year Change - Microsoft Fabric Community
I think this approach and the one suggested by Royel would work but if I put Month Year on my X axis, it would show filtered x axis for the selected month and not the mtd,qtd,ytd months for mom visual. Also, since this approach seems enticing, would like to understand the performance aspect of using direct sumx without calculate wrt to processing time and resources use
EDIT- Greg_Deckler Not using calculate is although easy to use and implement, when I 'm using it with large data with some filters applied, it is showing error of resources limit reached and not rendering the visual. Any possible solution for that?
v-sgandrathiCommunity Support
Hi peterpure,
Thank you for your thoughtful follow-up and for considering the suggestions from Community members.
When you place Month Year on the X-axis, it takes filter context directly from your slicer, which limits the visual to only the selected month. This setup conflicts with the goal of showing multiple periods like MTD, QTD, or YTD for Month-on-Month comparisons.
To address this, you can use a disconnected Month-Year table. This lets users make selections without filtering your Fact table directly. In these cases, create measures that read the selected period (MTD/QTD/YTD) from the disconnected slicer using SELECTEDVALUE, and apply custom filtering in your DAX measures with CALCULATE and REMOVEFILTERS or ALL. These functions help override slicer filters, enabling accurate period-over-period analysis across the full time axis. This way, you can display Month-on-Month growth metrics while maintaining the chosen aggregation level.
Regarding performance, using SUMX directly on a column can be more efficient for simple aggregations, but when you need to manipulate context, CALCULATE is necessary. For better performance, use variables (VAR) for intermediate calculations, avoid unnecessary context transitions, and use tools like Performance Analyzer in Power BI Desktop to test and optimize your model.
Thank you.- v-sgandrathi
Hi peterpure,
As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you.