Struggling with a Rolling Average

[SyCams]

Hey all!

I think I've exhausted every resource I can at this point and would really appreciate some help or a gentle shove in the right direction. 

I am attempting to just take a 5 week rolling average for values in a single column but can not seem to get anything to work. There are tons of questions asking similar things but the solutions just aren't quite working out for me. It could have something to do with using Direct Query or that I am not using actual dates with my rows.

All I'd really need is a 3rd column that takes the average of the previous 5 weeks but a lot of the solutions using VARs and DATEBETWEEN don't seem to properly address my issue. Any assistance would be greatly appreciate. Reminder - I am using live data from a direct query source.

(Also as a note - current week of year is a custom fiscal week and does not exactly correspond to the actual weeks of the year)

[rsbin]

@SyCams ,

I do something similar except I use specific Weekdays.

I have attempted to modify the code I use and apply it to your situation and field names.

TotalACT_5WkAvg = 
VAR _LastWeek = MAX( YourTable[Current week of year] )
VAR _Duration = 4           // Prior 4 Weeks + _LastWeek gives 5 Periods
VAR _FirstWeek = _LastWeek - _Duration
VAR _CalculationPeriod = // Isolate the 5 Prior Periods
                                FILTER( ALL ( YourTable ),
                                  AND (YourTable[Current week of year] >= _FirstDate,
                                       YourTable[Current week of year] <= _LastDate ))
                                       

VAR _MovingAverage = CALCULATE (AVERAGEX( [Sum of TotalAct], _CalculationPeriod )

RETURN
       _MovingAverage

Where [sum of TotalAct] is a Measure.

The syntax may not be perfect ( I don't have the chance to test it), but I think with the comments I have put in, you can trace the logic and correct anything amiss.  If you are unable to get it, please post a small sample of your data as a table (not as a picture) and I might get a chance later to do any debugging.

Good Luck and Regards,

[SyCams]

Hey, thanks for the reply! It seems to work up until the [sum of TotalAct] portion. It won't select a measure as a paramter here and errors the code. I created a seperate measure that was just SUM(TotalAct) but can't use it in the AVGX

[amitchandak · Accepted solution]

@SyCams , if you have date then you can

Rolling 30 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-30,DAY))

if you have year week , then if you can get rank column in that (in direct query you can create a date table import mode)

column - dax if date is in import mode. or create at source

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures

Last 5 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-5 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

[SyCams]

Thank you for the reply.

I realized I didn't even need to create anything related to dates since my "Current Week of Year" (now abr to CURWK acted as an already ranked Year Week. 

I was able to use your formula and substitute in CURWK for Week Rank and then do the division at the end and viola, it worked for me. so THANK YOU!

ROLAVG = CALCULATE(sum('PURCH_Weekly Sales By Buyer'[TOTALACT]),FILTER(ALL('PURCH_Weekly Sales By Buyer'),'PURCH_Weekly Sales By Buyer'[CURWK]>=MAX('PURCH_Weekly Sales By Buyer'[CURWK])-5 && 'PURCH_Weekly Sales By Buyer'[CURWK]<=MAX('PURCH_Weekly Sales By Buyer'[CURWK])))/6