Taking average of last 4 rows in a dataset

[minnielahoti]

I am trying to take the average of the "Total Cores" for the latest 4 weeks. The table on the left shows the date field as numeric, which is what I used in the calculation for creating a Measure to calculate the average. However, it returns the the most recent Core value and not the average of the last 4 week total core values. Can someone please help me figure out my mistake? Thank you!

[Anonymous · Accepted solution]

Hi @minnielahoti ,

According to your statement, I know you want to calcualte the average in the latest 4 weeks. However, you filter from the max [Date_num] and [Date_num]  - 4 (20220617 to 20220613). I think it is incorrect. We only have 20220617 in range, so it returns the the most recent Core value.

Please try my code as below.

4_weeks_Avg = 
VAR _4WEEKEND =
    MAXX ( ALL ( CoresByDate ), 'CoresByDate'[Date] )
VAR _4WEEKSTART = _4WEEKEND - 4 * 7
RETURN
    CALCULATE (
        AVERAGE ( CoresByDate[TotalCores] ),
        FILTER (
            ALL ( CoresByDate ),
            CoresByDate[Date] <= _4WEEKEND
                && CoresByDate[Date] > _4WEEKSTART
        )
    )

Result is as below.

Best Regards,
Rico Zhou

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