Forum Discussion
Struggling with a Rolling Average
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)
Anonymous , 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])))
4 Replies
- amitchandak
Super User
Anonymous , 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])))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
- rsbin
Community Champion
Anonymous ,
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 _MovingAverageWhere [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,
