12 Week Rolling Average without Dates
Good morning and Happy New Year, I have been wracking my brains for a while now on how to solve a problem. What I need to do is create a rolling 12 week average, which I seem to have done but the problem lies with the initial 12 weeks of the selected financial year. Rather then looking back at the 12 weeks in the previous financial year, its start off at week 1 and accumulates the average until it gets to week 12. Hopefully the visual below explains it better. Ideally, not sure if its possible in Power Bi to generate values in years that arent selected in the slicers, but I would want week 1 to be an average of the previous 12 weeks in the last financial year even if its not been selected so the user can select a year at a time rather then multiples. I have put the DAX below. _12W_RA = VAR CurrentWeek = MAX(DIM_CALENDAR[WEEK_OFFSET]) RETURN CALCULATE( SUMX( FILTER( ALLSELECTED(FCT_LCHID), FCT_LCHID[DIM_CALENDAR.WEEK_OFFSET] <= CurrentWeek && FCT_LCHID[DIM_CALENDAR.WEEK_OFFSET] >= CurrentWeek - 11 ), FCT_LCHID[LOST_CUSTOMER_HOURS] ) )/12 Any help would be great. Thanks Rich
