Need help with Two Date Filter Contexts on Same Page

[peterpure]

I have a factSales table and a date dimension table in my model. I have given two kinds of slicers - A. dropdown of Month Year B. Disconnected Table slicer of MTD, QTD and YTD. My objective is to show two kinds of charts-
1. Total Growth of sales for each MTD, QTD and YTD period based on month year dropdown selected by user
2. Month on Month growth of sales for the same MTD, QTD and YTD selected by the user for the same Month Year selected

I have solved first requirement by using DateAdd function in calculate and I'm changing it using switch to suit current MTD, Qtd and YTD selection. However, my second requirement is not getting met since the slicer is just selecting the current month and I'm not able to put three months for quarter or 12 months for year since the month year filter already has filtered my sales table. Now I don't know how to get around this to show both types on same page with same set of slicers.

[Royel]

Hi @peterpure 

You are having this issue because when the month year filter is applied, it already filters your sales table, making it impossible to show multiple months for MTD, QTD and YTD calculations. 

As @Greg_Deckler mentioned you can use ALL to remove the applied filters in a table. 

Example for All: 

Month on Month Growth = 
VAR CurrentPeriodSales = [Total Sales]
VAR PreviousMonthSales = 
    CALCULATE(
        [Total Sales],
        DATEADD('Date'[Date], -1, MONTH),
        REMOVEFILTERS('Date'[MonthYear])  -- Remove the month filter context
    )
RETURN
DIVIDE(CurrentPeriodSales - PreviousMonthSales, PreviousMonthSales)

 There is another way to achieve the same results by using REMOVEFILTERS

Example REMOVEFILTERS:

Month on Month Growth = 
VAR CurrentPeriodSales = [Total Sales]
VAR PreviousMonthSales = 
    CALCULATE(
        [Total Sales],
        DATEADD('Date'[Date], -1, MONTH),
        REMOVEFILTERS('Date'[MonthYear])  -- Remove the month filter context
    )
RETURN
DIVIDE(CurrentPeriodSales - PreviousMonthSales, PreviousMonthSales)

Is it helped? ✔ Give a Kudo • Mark as Solution – help others too!

[Greg_Deckler]

@peterpure 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-TIT...

Also, this is for YoY but MoM is effectively the same: Better Year Over Year Change - Microsoft Fabric Community

[peterpure]

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-sgandrathi · Accepted solution]

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.