Forum Discussion
Moving Average with multiple criteria
Hello guys,
I am a begginer in PowerBi and I have been struggling to calculate an average that doesn't sound very difficult, but I can't get it right...
I have a database of sales for diferent clients and products throughout several months.
It Looks something similar to this:
So, for example, in the 4th line, for sales in April (Abril), I would like to calculate a column for the average sales of that product, for that client, for the three previous months (Jan, Fev, Mar).
For the 6th line, it would be the same thing, but considering Mar, Apr and May. And so on for each client/product/month.
Does anyone have an idea of how to make this work?
Thanks a lot! :)
3 Replies
Hi Anonymous,
You can try to use below measure if it suitable for your scenario:
Rolling 3M = VAR currDate = SELECTEDVALUE ( Table[Month] ) VAR monthNum = INT ( VALUE ( FORMAT ( DATEVALUE ( currDate ), "m" ) ) ) RETURN IF ( monthNum < 3, BLANK (), CALCULATE ( SUM ( Table[Sales] ), FILTER ( ALLSELECTED ( Table ), INT ( VALUE ( FORMAT ( DATEVALUE ( [Month] ), "m" ) ) ) >= monthNum - 3 && INT ( VALUE ( FORMAT ( DATEVALUE ( [Month] ), "m" ) ) ) <= monthNum ), VALUES ( Table[Client] ), VALUES ( Table[Product] ) ) )Regards,
Xiaoxin ShengHey Xiaoxin, Thanks a lot!
That is great!
For what I understood of the formula, it fits perfectly to my case.
However, I am getting an error that I cannot find the root cause.
I couldn't find the translation, but it says something like "Not possible to convert the velue " from type Text to type Number"
I imagine it may be because my sales column sometimes have blank values, but I couldn't figure out how to fix it.
Have you ever seen this error? How could I just ignore the " values this error is reffering?
Thanks again!