Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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! 🙂
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 Sheng
Hey 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!
Hi @Anonymous,
It seems like my formula can't works on your data, it faced an issue when it transform your data from month name to number.
Can you please share a pbix file to test and coding formula? You can upload to google drive or onedrive and share link here.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |