Running Total of a Moving Average

senterlm — Fri, 19 Aug 2022 06:15:27 GMT

There many post on Running Totals and Moving Averages separately.

I have successfully used moving average to extrapolate a future forecast

Cost Moving Average = AVERAGEX(DATESINPERIOD('calendar'[date_field],CALCULATE(LASTDATE('calendar'[date_field]),All('calendar'[fiscal_month_desc])),-4,MONTH),sum(cost[Cost]))

but the Running Total measure of the moving average measure returns the same result as the Moving Average, not the cummulative sum of the Moving Average for the filter context(looking quarter by quarter)

Quarter Cumulative Cost Moving Average = CALCULATE([Cost Moving Average],FILTER(all('calendar'),'calendar'[date_field]<=max('calendar'[date_field])))

I assume this is because there are not real values for these period in the fact table, or the Moving Average isn't coming from a real fact table. I tried to create a query table for the moving average by the fields I want to be able to slice on, but was unsuccessful. Any ideas?

Re: Running Total of a Moving Average

Jihwan_Kim — Fri, 19 Aug 2022 07:40:08 GMT

Hi,

I am not sure how your expected visualization looks like, but could you please try the below whether it suits your requirement?

Quarter Cumulative Cost Moving Average = CALCULATE ( SUMX ( VALUES ( 'calendar'[date_field] ), [Cost Moving Average] ), FILTER ( ALL ( 'calendar' ), 'calendar'[date_field] <= MAX ( 'calendar'[date_field] ) ) )

topic Re: Running Total of a Moving Average in DAX Commands and Tips

Running Total of a Moving Average

Re: Running Total of a Moving Average