Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Moving or Rolling Average KPI

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.

  • amitchandak's avatar
    amitchandak
    3 years ago

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.