Caclulate function over riding filter?

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,