Forum Discussion
Cumulative Fiscal week DAX Calc
Hi Anonymous
As you mentioned , you want to calculate the Cumulative prior year till 52 weeks instead of 48 weeks. I took sample data based on your inputs and replicated from my side. Please try to use these measures , you will get the desire output. Please refer below output snaps and attached .PBIX file.
Thanks.
Super UserHi Anonymous
I repdroduce here and you just create a measure:
Current Year Cumulative =
VAR CurrentWeek = MAX('Customer table'[FiscalWeek])
VAR CurrentYear = MAX('Customer table'[FiscalYear])
RETURN
CALCULATE(
SUM('Customer table'[Value]),
FILTER(
ALL('Customer table'[FiscalWeek]),
'Customer table'[FiscalWeek] <= CurrentWeek
)
)
If this answer was helpful in any way, I would be pleased to receive a 👍, as well as the satisfaction of seeing a DAX measure work for the first time without needing yet another FILTER.
Please mark it as the accepted solution. This helps other community members find the quickest path and saves them from another endless loop 🌀.
Working fine. previous year not works
my calendar filter = 25'Q4 then it will show last year cumulative numbers but showing as blank data but we have datas. DAX Code applied below
- Zanqueta
Hi Anonymous,
The issue lies in the way the filter context is being removed and applied. Your measure for Previous Year Cumulative returns BLANK because:- 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
- Confirm that the 'Calendar' table is related to 'Orders ESET Actuals' via the date or fiscal key.
- Ensure [Order_QTD $] is a measure that correctly sums values.
- 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
If you have large datasets, you can use DATESYTD or TOTALYTD with SAMEPERIODLASTYEAR: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 🌀.
Previous Year Cumulative
This DAX Function showing all 52 weeks for previous year
Ideally it should be calendar Filter = 25'Q4 then it will show only 24'Q4 From 40-52 weeks numbers with cumulative value
suggest cumulative value for previous year from 40-52 weeks on 24'Q4 Qtr
- amitchandak
Anonymous , you need to a change for that
Last year Week Cumm Selected = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),
'Date'[FY Year]=(max('Date'[FY Year]) -1) && 'Date'[Fy Week]>=Min('Date'[Fy Week]) && 'Date'[Fy Week]<=max('Date'[Fy Week]) ))
