topic Rolling Average for last 3 filtered entries in DAX Commands and Tips

Rolling Average for last 3 filtered entries

sfwannabe — Tue, 07 Jun 2022 11:26:36 GMT

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...

Re: Rolling Average for last 3 filtered entries

Icey — Fri, 10 Jun 2022 03:07:21 GMT

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.

Re: Rolling Average for last 3 filtered entries

sfwannabe — Sun, 12 Jun 2022 22:40:14 GMT

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?