topic Re: Caclulate function over riding filter? in DAX Commands and Tips

Caclulate function over riding filter?

fallingfirst — Sat, 05 Jul 2025 15:26:46 GMT

Measure

S&OP FY25 = CALCULATE([CY Act Vol May + S&OP Jun],'Date'[FY]="FY25")

My data consist of FY25 and FY26 data. The above measure will filter it to only FY25 data. However, when I slice this data by month (e.g. May). I end up getting May YTD instead of just May month results. How can I fix this?

Re: Caclulate function over riding filter?

FBergamaschi — Sat, 05 Jul 2025 16:16:01 GMT

Hi,

CALCULATE will surely override any filters on the column 'Date'[FY]. To understand your issue, though, I need some images of how you are grouping columns in the visual (eg what the filter context consists in), the code of the measure inside the CALCULATE call and your data model.

Some pasted data would help further so I can import in Power BI and reproduce the issue and find the solution.

Best, FB

Re: Caclulate function over riding filter?

DataNinja777 — Sat, 05 Jul 2025 16:12:10 GMT

Hi fallingfirst ,

The issue you're encountering, where slicing by a month results in a year-to-date (YTD) value, typically stems from the logic within your base measure, [CY Act Vol May + S&OP Jun]. This measure likely uses a time-intelligence function that calculates a cumulative total, which overrides the simple month filter from your slicer. To resolve this, you must modify your primary DAX formula to force it to respect the slicer's context.

You can correct this by adding the VALUES function to your existing formula. This explicitly reapplies the month selection as a filter, ensuring the calculation is constrained to just that period.

S&OP FY25 = CALCULATE( [CY Act Vol May + S&OP Jun], 'Date'[FY] = "FY25", VALUES('Date'[Month]) )

In this revised measure, VALUES('Date'[Month]) captures the month selected in your slicer and uses it as a direct filter in the CALCULATE function. This powerful addition overrides any internal YTD calculations within your base measure, giving you the specific monthly value you need.

While the above code provides an immediate fix, it's worth noting that the name of your base measure, [CY Act Vol May + S&OP Jun], suggests it may be hard-coded and inflexible. For a more robust and scalable solution, it is better practice to create a dynamic base measure that can distinguish between actual and forecast data based on the date context.

For example, you could create a new base measure that automatically selects the correct data type.

S&OP Volume (Dynamic) = VAR SelectedDate = MAX('Date'[Date]) -- Define the cut-off date for actuals VAR LastActualsDate = DATE(2025, 5, 31) RETURN IF( SelectedDate <= LastActualsDate, [Actual Volume], [S&OP Volume] )

Using this dynamic measure simplifies your final calculation considerably, making it cleaner and easier to maintain. This approach will work correctly across any month without requiring additional overrides.

S&OP FY25 = CALCULATE( [S&OP Volume (Dynamic)], 'Date'[FY] = "FY25" )

Best regards,

Re: Caclulate function over riding filter?

MasonMA — Sat, 05 Jul 2025 22:05:10 GMT

fallingfirst

Could you share how [CY Act Vol May + S&OP Jun] is defined? It’d help to know if it already has built-in logic for month or year-to-date — that could affect how filters like 'Date'[FY] = "FY25" or a month slicer interact with it.

Also, unless you have to use YTD for certain visuals, simplifying the base measure to avoid embedded time intelligence functions is generally a better long-term solution.

A measure like below to prevent unwanted context transition.

S&OP FY25 =
CALCULATE(
[SimplyAggregation],
'Date'[FY] = "FY25",
KEEPFILTERS('Date'[Month]) // Preserves the month selection
)

Re: Caclulate function over riding filter?

v-priyankata — Tue, 08 Jul 2025 03:46:25 GMT

Hi fallingfirst

Thank you for reaching out to the Microsoft Fabric Forum Community.

DataNinja777 FBergamaschi MasonMA Thank you for inputs.
As they suggested, please try the steps above as they can be helpful. If the issue still persists, feel free to reach out to the community for further assistance.

Thanks.

Re: Caclulate function over riding filter?

v-priyankata — Thu, 10 Jul 2025 05:51:36 GMT

Hi fallingfirst

I wanted to check if you had the opportunity to review the information provided by users. Please feel free to contact us if you have any further questions.

Re: Caclulate function over riding filter?

v-priyankata — Mon, 14 Jul 2025 10:02:40 GMT

Hi fallingfirst

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted If yes, marking the relevant solution would be awesome for others who might run into the same thing.