Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Rolling average ignore blank values

Hi everyone, 

 

I have a calculated column which returns the rolling average percentage of the last five times (based on the order of the index). 
However, sometimes there is a blank row, which will filled later. So, the index already has a value, but all other rows are blank. 

 

In the image, you see that Index 11 is empty. However, the percentage for Index 12 isn't the same. This doesn't make sense, because the column Percentage profit/loss is 3,27%, but the column Percentage Rolling Avg decreases (from -0,72% to -0,97%)
I would like that Index 11 should be ignored, so that the percentage for Index 12 is the same as Index 10 (-0,72%). 

The formula for the calculated column is as follows:

Percentage Rolling Avg =        
VAR Myindex = Trades[Index]
VAR RollingSum =
    AVERAGEX(
    FILTER(
        Trades,
        Trades[Index] > Myindex - 5 &&
        Trades[Index] <= Myindex),
        Trades[Percentage]
)

RETURN
IF(
    ISBLANK(Table[Percentage profit/loss]),
    BLANK(),
    RollingSum
)

Thanks in advance!



Need Help

2 Replies

  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    According to your calculation logic, when the index is 10, it is the average of row "6, 7, 8, 9, 10". When the index is 12, it is the average of row "12, 10, 9, 8, 7" which is not the same as row 10. And it does ignore blank values. 

    Could you please describe your calculation logic more clearly in order that we can help you better?

     

    Best Regards,
    Community Support Team _ xiaosun

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