Get a year to date view from only the last several weeks

[travbum]

I'm trying to build a graph that shows a year to date sum over each week for only the last 7 weeks. Currently I'm using this measure: 

Qty_Sold_YTD = CALCULATE(SUM(Data[Qty_Sold]), DATESYTD('Calendar'[Date]))

and using a custom column that calculates the week number to filter to only weeks that were between now and 7 weeks ago. However, when I apply these filters, all the data from before 7 weeks ago doesn't get included. I have this measure which I think should do it, I'm just not sure how to add this value to Qty_Sold_YTD:

QTY_Sold_Before_7_Weeks_Ago = 
CALCULATE(
	SUM(Data[Qty_Sold]),
	FILTER(
			Data,
			WEEKNUM(TODAY())-WEEKNUM(Data[Date])<7
	)
)

[v-caliao-msft · Accepted solution]

Hi @travbum,

To achieve your requirement, you can use the DAX below.

QTY_Sold_Before_7_Weeks_Ago = CALCULATE(SUM(data[Qty_Sold]),FILTER(ALL(data),data[Date]<=MAX(data[Date])&&(WEEKNUM(TODAY())-WEEKNUM(data[Date]))<7))

Regards,

Charlie Liao