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 belo...
  • 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

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.