cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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
)
)```
1 ACCEPTED SOLUTION
Employee

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

Employee

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