Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tomshaw83
Helper I
Helper I

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
    
    
1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@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

 

 

smpa01_0-1638291073808.png

 

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

 

smpa01_1-1638291375039.png

 

Pbix is attached

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

1 REPLY 1
smpa01
Super User
Super User

@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

 

 

smpa01_0-1638291073808.png

 

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

 

smpa01_1-1638291375039.png

 

Pbix is attached

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors