Forum Discussion

tomshaw83's avatar
tomshaw83
Icon for Helper I rankHelper I
4 years ago
Solved

Calculated column to display last N values

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

 

CityDateTempAverage Last 3 Temps for City
London21/7/20212119.6
New York21/7/20211818.3
London24/7/20212420.1
Paris25/7/20212221.1
London27/7/20212122
    
    
needhelp
  • smpa01's avatar
    smpa01
    4 years ago

    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

1 Reply

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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