I have a custom calendar table with date, year, week number, year week.

There is another table called FactSales

Example: 2024, 5, Y24 W05

I want to create a table visual with 4 columns. Year week, sales in that week, sum of sales in previous 30 weeks, sum of sales in previous 52 weeks.

Sales in that week = sum(FactSales['salesamt'])

Sales in previous 30 weeks = 
var curryear=MAX(calendar[year])
var currweek=MAX(calendar[week number])
Return CALCULATE([Sales in that week], calendar[week number]>=currweek-30)

Sales in previous 52 weeks = 
var curryear=MAX(calendar[year])
var currweek=MAX(calendar[week number])
Return 
CALCULATE([Sales in that week], calendar[week number]>=currweek-52)

I’m facing 2 problem with this approach.

One is I’m not sure how to take out the filter context of current row. For example – should I use ALL(calendar) or ALL(calendar[date]) or ALL(sales[saledate]) in the following DAX to get rid of the current week context of the table visual row.

CALCULATE([Sales in that week], calendar[week number]>=currweek-30)

The second problem is that – say we are in week 10. Subtracting 30 from 10 results in negative week number. How to handle such case?