Forum Discussion

vanessa's avatar
vanessa
Icon for Post Patron rankPost Patron
9 years ago

Dynamic Last 7 day

  I am trying to calculate the sales for the last 7 days, but the last 7 days will be dynamic, based on the date selected in a slicer. eg. if the date selected in the slicer is 2017/07/18, then the...
Need Help
Show and Tell
Rameshwar's avatar
Rameshwar
Frequent Visitor
9 years ago

Anonymous

 

The Calculated column will not work here. This Measure needs to be dynamic. The user might select any date from Slicer and depending upon that we should go back previous 7 days. 

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
9 years ago

Hey,

 

maybe you can give measure a try, it assumes that there is a separate Calendar table in your model

CALCULATE(
		SUM('FactWithDates'[Amount])
		,ALL('Calendar'[Date])
		,DATESBETWEEN('Calendar'[Date],MAXX(VALUES('Calendar'[Date]),'Calendar'[Date]) - 7 , MAXX(VALUES('Calendar'[Date]),'Calendar'[Date])))
)

Hope this works for you

 

Regards