Cumulative Fiscal week DAX Calc

Hi Anonymous,

• You are using ALL('Orders ESET Actuals'[Order_Fiscal week], 'Orders ESET Actuals'[fiscal year]), which removes all filters, but then applies conditions that may not align with the current context.
• If the calendar filter is set to 2025 Q4, the measure looks for weeks from the previous year (2024) but within the original table context, which may not be correctly related to the calendar table.

Use the calendar table to control the context and remove only the necessary filters (rather than removing everything). Example:

Previous Year Cumulative =
VAR CurrentWeek = MAX('Calendar'[FiscalWeek])
VAR PreviousYear = MAX('Calendar'[FiscalYear]) - 1
RETURN
CALCULATE(
    [Order_QTD $],
    FILTER(
        ALL('Calendar'),
        'Calendar'[FiscalYear] = PreviousYear &&
        'Calendar'[FiscalWeek] <= CurrentWeek
    )
)

Checklist to make this work

1. Confirm that the 'Calendar' table is related to 'Orders ESET Actuals' via the date or fiscal key.
2. Ensure [Order_QTD $] is a measure that correctly sums values.
3. If your filter is by quarter (25'Q4), the logic remains valid because we use MAX(FiscalWeek) to determine the cumulative range.

Alternative for performance

Previous Year Cumulative =
CALCULATE(
    [Order_QTD $],
    DATESYTD(SAMEPERIODLASTYEAR('Calendar'[Date]), "30/06")
)

(Assuming the fiscal year ends on 30 June.)

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.