peterr2006
New Member
- Joined
- Jan 2, 2024
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
I am trying to calculate the MAD between a 6 month rolling average (R6M) and the actual sales. I have used a formula to calculate a 6 month rolling average with a 3 month offset in a power pivot measure but when I go to calculate the MAD R6M it either gives me the actual sales or zero.
My data table has the columns month (in the format YYYMM), Actual Sales and the Date (being the 1st of the month).
My measures are as follows:
Actual:=sum([Sales])
Average Sales:=AVERAGEX(VALUES(Table1[Month]),[Actual])
R6M:=CALCULATE([Average Sales],filter(all('Table1'[Month]),'Table1'[Month]),DATESINPERIOD('Table1'[Date],DATEADD(PREVIOUSMONTH(LASTDATE('Table1'[Date])),-3,MONTH),-6,MONTH))
AD R6M:=abs([Actual]-[R6M])
MAD R6M:=SUMX('Table1',[AD R6M])
I have attached a screenshot of my pivot table results.
My data table has the columns month (in the format YYYMM), Actual Sales and the Date (being the 1st of the month).
My measures are as follows:
Actual:=sum([Sales])
Average Sales:=AVERAGEX(VALUES(Table1[Month]),[Actual])
R6M:=CALCULATE([Average Sales],filter(all('Table1'[Month]),'Table1'[Month]),DATESINPERIOD('Table1'[Date],DATEADD(PREVIOUSMONTH(LASTDATE('Table1'[Date])),-3,MONTH),-6,MONTH))
AD R6M:=abs([Actual]-[R6M])
MAD R6M:=SUMX('Table1',[AD R6M])
I have attached a screenshot of my pivot table results.