Caclulate function over riding filter?

[fallingfirst]

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?

[v-priyankata]

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.

[v-priyankata]

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.

[MasonMA]

@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
)

[DataNinja777]

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,

[FBergamaschi]

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