Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

 

image.png

 

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 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.