Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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
@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:
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.
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 ,
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |