I am trying to create a simple moving average on power BI to calculate the savings on a purchasing area for each part number (numero do item) on a table. The Idea is to use the moving average from the previous year as the baseline to campare the prices for the current year (ex: 2021 moving average will serve as baseline for 2022) . To achieve this goal I'm using the following formula:
SMA - SIMPLE MOVING AVERAGE =
if(
ISBLANK('Medidas'[Média Ponderada]),BLANK(),
AVERAGEX(
DATESINPERIOD(dCalendario[Data],
LASTDATE(dCalendario[Data]),-1,YEAR),
CALCULATE(sumx('dCalendario',Medidas[Média Ponderada])
)
)
)
The problem is that this formula is including the current year on the calculation creating a distortion on the baseline to compare how much more or less we paid comparing with the last year.