Forum Discussion

Busno's avatar
Busno
Helper I
5 years ago
Solved

Rolling average 30 day

Dear all, I need some help here. I want to create a 30 day rolling average. I have a daily sales record In column C and I want to average every 30 days like column D How can I do it in Power Bi? ...
Need Help
Tutorial Requests
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    Hi Busno ,

     

    If you need to calculate according to the calendar, it should be like this.

    DATESINPERIOD() function takes into account dates that do not exist in your table.

    Measure = 
CALCULATE(
    AVERAGE(Sheet3[C]),
    DATESINPERIOD( Sheet3[A], MAX(Sheet3[A]), -30, DAY ),
    ALL(Sheet3)
)

    If you need to calculate according to the number of days in the table, it should be like this.

    Measure 2 = 
CALCULATE(
    AVERAGE(Sheet3[C]),
    FILTER(
        ALL(Sheet3),
        Sheet3[Index] <= MAX(Sheet3[Index]) && Sheet3[Index] >= MAX(Sheet3[Index]) - 29
    )
)

    Please refer to my .pbix file.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

ChiragPatnaik's avatar
ChiragPatnaik
Regular Visitor
2 years ago

Hello, I used this to create a rolling average . 
I am looking to create a rolling average of volume (TTL_TRD_QTY) for each symbol. and then set a flag if today's/(the day's) volume for the symbol is higher than this average. 

This is what I have right now. 

 

 

=CALCULATE(
    AVERAGE(Delivery[TTL_TRD_QNTY]),GROUPBY(Delivery,Delivery[SYMBOL]),
       FILTER(
        ALL(Delivery),
        Delivery[DATE1]<=MAX(Delivery[DATE1]) && Delivery[DATE1] >= MAX(Delivery[DATE1])-35
    )
)

 


Once I get this in the calculated filed, then it should be a trivial matter to add another field with the flag with a if statement.  

I seem to have got the first part right by using GroupBy as you can see in this. 

 

However when I add a If function, it gives a circular reference error.


Is there any way to overcome this?

foodd's avatar
foodd
Community Champion
2 years ago

Hello ChiragPatnaik , please create a new post with your question, instead of replying to a post that is four years old and already has an Accepted Solution.