Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Rolling Average and Variance DAX Measures

Hi, I need help creating two new measures.  1) Rolling average of amount earned for each Site (Site can be selected via Slicer). There are 23 sites if that matters.  2) Weekly Variance (Date can be...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
7 years ago

Try Like

Current Sales = ( 
VAR _Cuur_start = Min('Date'[Date]) -- Or choose week start date
VAR _Curr_END = Max('Date'[Date ])
VAR _WEEK_NUM = MAXX(FILTER('Date'[Date] =_Curr_END),'Date'[Week of year])
return 
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <=  _Curr_END )
)



Prior Sales = ( 
VAR _WEEK_NUM = MAXX(FILTER('Date'[Date Filer] =_Curr_END),'Date'[Week of year]) -1
return 
calculate(sum(Sales[Sales]),'Date'[WEEK OF YEAR] >= _WEEK_NUM )
)