Calculated column to display last N values

[tomshaw83]

Hi all,

I'm looking for some help creating a calculated column, whereby the average of the last 3 values recorded for a city are shown.

I'm getting lost somewhere after AVERAGEX(TOPN,3 - if that is even the right way to start to solve...

City	Date	Temp	Average Last 3 Temps for City
London	21/7/2021	21	19.6
New York	21/7/2021	18	18.3
London	24/7/2021	24	20.1
Paris	25/7/2021	22	21.1
London	27/7/2021	21	22

[smpa01 · Accepted solution]

@tomshaw83  you can use this measure

Measure =
VAR _upper =
    MAX ( 'Table'[Date] )
VAR _city =
    MAX ( 'Table'[City] )
VAR _lower =
    MINX (
        TOPN (
            3,
            FILTER ( ALL ( 'Table' ), 'Table'[City] = _city && 'Table'[Date] <= _upper ),
            'Table'[Date], DESC
        ),
        'Table'[Date]
    )
VAR _avg =
    CALCULATE (
        AVERAGE ( 'Table'[Temp] ),
        FILTER (
            ALL ( 'Table'[Date] ),
            'Table'[Date] >= _lower
                && 'Table'[Date] <= _upper
        ),
        ALLEXCEPT ( 'Table', 'Table'[City] )
    )
RETURN
    _avg

Calculated column

Column = 
VAR _upper =
    CALCULATE(MAX ( 'Table'[Date] ))
VAR _city =
    CALCULATE(MAX ( 'Table'[City] ))
VAR _lower =
    MINX(
        TOPN (
            3,
            FILTER ( ALL ( 'Table' ), 'Table'[City] = _city && 'Table'[Date] <= _upper ),
            'Table'[Date], DESC
        ),'Table'[Date])
VAR _avg =
    CALCULATE (
        AVERAGE ( 'Table'[Temp] ),
        FILTER (
            ALL ( 'Table'[Date] ),
            'Table'[Date] >= _lower
                && 'Table'[Date] <= _upper
        )
    ,ALLEXCEPT('Table','Table'[City]))
RETURN
    _avg

Pbix is attached