Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
travbum
Advocate I
Advocate I

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
v-caliao-msft
Employee
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))

Capture.PNG

 

Regards,

Charlie Liao

View solution in original post

1 REPLY 1
v-caliao-msft
Employee
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))

Capture.PNG

 

Regards,

Charlie Liao

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.