Exclude A Matrix Column From Interacting With A Date Slider

[rbowen]

I have a matrix visual showing a mix of products and related values for quantities on hand, quantitites on order, etc for each product. There is a date slider spanning multiple fiscal years controlling the matrix so that users have an historical view of of these columns based on what date range they select with the date slider. I'd like to add another column to the matrix that shows the current fiscal year to date values for all products that won't change or disappear altogether regardless of what date range a user selects in the date slider. I created a calculated column in my sales/quantity table capturing the current fiscal year quantity for all products:

CurrentFYQty = IF([Date] >= DATE(2024,9,1) && [Date] <= DATE(2025,8,31), [Transaction Quantity])

Then, I created a measure to sum these values:  CFYQtySum = CALCULATE(SUM('Sales'[CurrentFYQty]))

This works fine in a separate matrix visual where the Edit Interactions option is set to not affect that matrix. However, when I place the measure in the matrix with the other measures/columns, it of course changes when the date slider is moved around (or becomes blank altogether). I need the column using the current fiscal year quantity measure to remain static and just show the current year to date product quantity values regardless of what range a user selects in the date slider.  I've read elsewhere this might be possible using either the ALL or REMOVEFILTER options somehow with my date table but I'm uncertain of what the correct syntax is. The date slider uses values from my date table. There's also a date column in my sales table. Can this be done?

Thank you.

EDIT:  I may be getting a little closer to what I'm after but still not able to get the YTD quantity column to remain completely static. I created a measure, YTDQty2 using the following syntax:

YTDQty2 = CALCULATE(SUM('Sales'[CurrentFYQty]),REMOVEFILTERS('Date'[Calendar Date]))

The left hand table is what I need, the right hand table is for comparison and is not affected by the date slider. With the date slider at its default range, the new measure matches the right hand table total values. 

However, when moving the date slider to a range that isn't part of the current fiscal year, the column goes blank.

[rbowen · Accepted solution]

I discovered the solution to the issue and it was ridiculously simple. I was using the wrong source table for my date slider. The REMOVEFILTERS does work now. This was definitely a facepalm moment on my part. Thank you @Ritaf1983 and @Anonymous for attempting to help. Should have had more coffee before posting this one. 

[Anonymous]

Hi @rbowen 

Following your description, I tested it using simple data and the measure works well in the matrix.

As Ritaf1983 mentioned, please provide some sample data and explain your matrix construction process in as much detail as possible (DAX formulas for other metrics used in the matrix, other table fields that may be used, relationships between tables, etc.). This will allow us to clarify the cause of the problem as soon as possible and make suggestions.

YTDQtyStatic = CALCULATE(SUM('Sales'[CurrentFYQty]), REMOVEFILTERS('Date'[Calendar Date]))

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rbowen]

Jarvis - 

Does your value stay static if you move the right hand side of the date slider to the left?

[Ritaf1983]

Hi @rbowen 

Please provide a workable sample data and your expected result from that. It is hard to figure out what you want to achieve from the description alone.  

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...