Top and top -1 values of list dynamically

[Anonymous]

Hello all,

I have a list of persons that have a score and a date of that score. Now I want to create a table in powerbi desktop with a bunch of things including their latest score and the score they have gotten before that dynamically.

I have this sample set:

Maya	9/21/2022	5.4
Robbert	9/20/2022	5
Vince	9/20/2022	3.4
Vince	3/12/2021	3.6
Vince	6/5/2022	3.7
Robbert	9/12/2022	2.9
Robbert	5/5/2021	5.6
Lisa	6/12/2022	9.9
Vince	7/12/2022	19.2
Lisa	8/20/2022	2.1

The result i would want to be like this:

Maya	Department 1	Address	5.4	NULL/EMPTY/0
Robbert	Department 2	Address	5	2.9
Vince	Department 2	Address	19.2	3.4
Lisa	Department 3	Address	2.1	9.9

I've looked into ranking etc, but i can't quite figure out how to do it in this specific context also because I want to put it in a table with other info.
When new data is added in the sample set i'd like it to automatically still pick the " top" and " top -1" value in the table.

If any more info is req let me know!

Thx!

[PaulDBrown · Accepted solution]

Use these measures:

Score on last date =
VAR _LastDate =
    CALCULATE ( MAX ( fTable[Date] ), ALLEXCEPT ( fTable, fTable[Employee] ) )
RETURN
    CALCULATE (
        SUM ( fTable[Score] ),
        FILTER ( ALLEXCEPT ( fTable, fTable[Employee] ), fTable[Date] = _LastDate )
    )

Score on last date -1 =
VAR _LastDate =
    CALCULATE ( MAX ( fTable[Date] ), ALLEXCEPT ( fTable, fTable[Employee] ) )
VAR _PrevDate =
    CALCULATE (
        MAX ( fTable[Date] ),
        FILTER ( ALLEXCEPT ( fTable, fTable[Employee] ), fTable[Date] < _LastDate )
    )
RETURN
    CALCULATE (
        SUM ( fTable[Score] ),
        FILTER ( ALLEXCEPT ( fTable, fTable[Employee] ), fTable[Date] = _PrevDate )
    )

[AUDISU]

@Anonymous 

Hi,

There is no Depatment and Address in your sample table. How did you get that?

Do you have that data in same table?

Thanks

[Anonymous]

Hey, the department etc is just an example of other info in my table. the measure is only about the last scores that i want to " append" to my existing table in powerbi. The data model is set up so there is a relationship between an employee and a score so it can get a score per employee. But i want the last and second to last score per employee based on the lastest date of the score if that makes sense.