topic Moving or Rolling Average KPI in DAX Commands and Tips

Moving or Rolling Average KPI

Anonymous — Wed, 01 Feb 2023 18:40:52 GMT

Dear comunity friends.

I am trying to create a saving KPI for a procurement department in power BI based on a moving average of the cost paid for the items.

To reach this goal I did use the below DAX formula to calculate the moving average of the values with a certain sucsess, but I need something a little more complex, I need the formula to calculate the moving average for the last year and then to use it as a base for the whole next year.

The table I have is this one:

And the formula I am using is this one:

SMA - SIMPLE MOVING AVERAGE = if(ISBLANK('Medidas'[Média Ponderada]),BLANK(),AVERAGEX( DATESBETWEEN(dCalendario[Data],MAX(dCalendario[Data])-366,MAX(dCalendario[Data])-30), CALCULATE(sumx('dCalendario',Medidas[Média Ponderada]))))

From the table I'd like for example that the last moving average of 2021 in the example 335,77 to be the moving average for the whole 2022.

Hope you can help me.

Re: Moving or Rolling Average KPI

amitchandak — Thu, 02 Feb 2023 02:23:37 GMT

Anonymous ,

YTD Avg = CALCULATE(AvergaeX(Values('Date'[Month Year]), CALCULATE(SUM(Sales[Sales Amount]))),DATESYTD('Date'[Date],"12/31"))

next YTD Avg = CALCULATE(AvergaeX(Values('Date'[Month Year]), CALCULATE(SUM(Sales[Sales Amount]))),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Re: Moving or Rolling Average KPI

Anonymous — Thu, 02 Feb 2023 10:52:50 GMT

Hello Amit. Thanks for your prompt help. I'll adapt your formula to my data and I'll let you know the results.

Thanks again.

Re: Moving or Rolling Average KPI

Anonymous — Thu, 02 Feb 2023 15:10:35 GMT

amitchandak I've been able to use it but dates are not matching and I think it is because I do not have the 'Date'[Month Year] on my calendar table. In your calendar table do you use january/23 or 01/23? I'll add also a colum with the format you use this way I think it will work.

The formula now is:

YTD Avg =

IF(ISBLANK('Medidas'[Média Ponderada]),BLANK(),

CALCULATE(AVERAGEX(VALUES(dCalendario[Data]),

CALCULATE(SUM('Relatório de Recebimento'[Preço Unitário S/Imposto]))),

DATESYTD(DATEADD(dCalendario[Data],-1,Year),"12/31")))

The result was:

The last column is the one with the formula, and if you can see it on january 2023 it isn't showing any data.

Hope you can help me once again.