Moving Average Query

[Anonymous]

Hi i'm new to PowerBI and trying to build my first dashboard.

Pulling my hair out with the moving average! I'm just trying to copy a graph I normally do in an excel pivot averaging the totals below (only an example, the datset is larger)  in bars and the 12 month moving average in a line.

I've tried the below measures from researching online:

Moving x months avg 1 = calculate(average('Scrapped'[Total]),DATESINPERIOD('Scrapped'[Date],LASTDATE('Scrapped'[Date]),-12,MONTH))

Moving x months avg 2 = CALCULATE(AVERAGEX('Scrapped','Scrapped'[Total]),DATESINPERIOD('Scrapped'[Date],LASTDATE('Scrapped'[Date]),-12,MONTH))

12mma_Scrap_Life1 =

var CurrDate = max('DateTable'[Date])

var PreviousDate = date(year(Currdate),Month(Currdate)-12,day(Currdate))

var Result =

Calculate(

    AVERAGEX('Scrapped','Scrapped'[Total]),

    filter(Scrapped,

    Scrapped[Date]>=PreviousDate && Scrapped[Date]<=CurrDate

    )

)

return

Result

where

DateTable = DISTINCT('Scrapped'[Date])

As well as the quick measure rolling avearge. No matter what i do the 12 month moving average calculates the same as the month average on the graphs.  Not sure what i'm missing. Can anyone please help?

Date	Total
31-Oct-2018	7642
12-Aug-2018	6052
17-Nov-2018	10058
02-Sep-2018	8596
06-Dec-2018	3863
16-Feb-2019	7872
15-May-2018	6366
13-Jan-2019	6399
16-Feb-2019	7272
20-Jan-2019	3551
03-Feb-2019	3181
04-Aug-2018	6638
06-Feb-2019	8197
01-Jan-2019	5499
17-Nov-2018	5442
15-Oct-2018	5739
25-Nov-2018	4577
06-Dec-2018	4181
06-Dec-2018	7053
16-Feb-2019	2596
13-Aug-2018	5918
08-Jan-2019	6847
14-Oct-2018	7526
15-Oct-2018	7739
20-Jan-2019	7270
28-Nov-2018	7566
22-Dec-2018	5519
15-Sep-2018	2630
24-Jun-2018	7032
23-Oct-2018	6888
11-Aug-2018	4629
12-Aug-2018	6309
04-Jan-2019	6898
18-May-2018	7425
19-Nov-2018	5065
03-Nov-2018	7038
30-Dec-2018	5745
08-May-2018	4908
16-Feb-2019	6197
23-Apr-2018	7981
12-Aug-2018	6306
15-Jul-2018	1214
14-Dec-2018	6530
30-Dec-2018	2540
14-Oct-2018	5676
24-Feb-2019	8688
30-Aug-2018	8881
15-Oct-2018	3825

[Ashish_Mathur]

Hi,

In your DateTable, create Year and Month columns.  To your visual, drag Year and Month from the DateTable.  Try this measure for moving average

=AVERAGEX(DATESBETWEEN('DateTable'[Date],EDATE(MIN('DateTable'[Date]),-11),MAX('DateTable'[Date])),'Scrapped'[Total])

Hope this helps.

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can try create measures like DAX below.

Period End = LASTDATE('DateTable'[Date])

Period Start= FIRSTDATE( DATESINPERIOD('DateTable'[Date], [Period End], -12, MONTH))

12mma_Scrap_Life1 = CALCULATE(AVERAGEX('Scrapped', 'Scrapped'[Total]),DATESBETWEEN ( 'DateTable'[Date], [Period Start], [Period End] ))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much it works a million.

I've also managed to get the quick measure 'rolling average' to work.  The moving  average value resulting from the formula you shared is correct, however it does not match the latest rolling average value on my graph....