Rolling Average for last 3 filtered entries

[sfwannabe]

Hello,

I have a table with muiltiple columns, i would like to have a rolling average measure that can bne displayed in a card.

I have a table as shown with a ObjectKey column which is a consecutive increment number.

I have a weight column that i would filter using slicers on Line, Scale and Station.

Once i select the slicer values i would like a card to display the rolling average for the last 3 entries for that selection.

I have the following code so far, but seems to be far from what i want to achieve.

Rolling average = 
VAR _lastrow = CALCULATE(MIN('processlog'[ObjectKey]),FILTER(ALL('processlog'),[Weight]=BLANK()))
RETURN
CALCULATE(AVERAGE('processlog'[Weight]),FILTER(ALL('processlog'),[ObjectKey]<=_lastrow-1&&[ObjectKey]>=_lastrow-3))

Any advice is most welcome...

[sfwannabe]

So my rolling average is now good... however,  i would like to display the rolling average for each station...

But some stations dont have data, how do i get the filter working on teh card if i cant select the missing data card?

[Icey · Accepted solution]

Hi @sfwannabe ,

You can try this:

Rolling average = 
VAR _lastrow =
    CALCULATE ( MIN ( 'processlog'[ObjectKey] ), ALLSELECTED ( 'processlog' ) )
RETURN
    CALCULATE (
        AVERAGE ( 'processlog'[Weight] ),
        'processlog'[ObjectKey] >= _lastrow,
        'processlog'[ObjectKey] <= _lastrow + 2
    )

However, the measure above will only return values when the filtered ObjectKey is consecutive. If the filtered ObjectKey is intermittent, it is suggested to use TOPN function. And it can also work for consecutive ObjectKey.

Rolling average 2 = 
VAR _last3row =
    TOPN ( 3, ALLSELECTED ( processlog ), [ObjectKey], ASC )
RETURN
    AVERAGEX ( _last3row, [Weight] )

Best Regards,

Icey

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