second latest value

[Anonymous]

Hi guys

I am quite new to Power BI and I am struggling now with getting the value from the second latest date. So i have a table "Gesamt"  like the following

Name Code	Datum	Gewicht	DIFFDate
Person 1	15.01.2024	88	31
Person 1	16.01.2024	89	30
Person 1	17.01.2024	87	29
Person 1	18.01.2024	88.5	28
Person 1	19.01.2024	89.5	27
Person 1	20.01.2024	87.5	26
Person 2	21.01.2024	60.5	25
Person 2	22.01.2024	60	24
Person 2	23.01.2024	61	23
Person 2	24.01.2024	59.5	22
Person 2	15.01.2024	59	31
Person 3	13.01.2024	70	33
Person 3	14.01.2024	71	32
Person 3	15.01.2024	69	31
Person 3	16.01.2024	69.5	30
Person 3	17.01.2024	70.5	29
Person 3	18.01.2024	71.5	28
Person 3	24.01.2024	72	22

I calculated the newest value of "Gewicht*  with 

GEWakt = CALCULATE(AVERAGE(Gesamt[Gewicht]),Gesamt[DIFF Date]=MIN(Gesamt[DIFF Date])) with DIFF Date as the difference

between today and the table date for each row.

Now I want to calculate the second newest value. For that I tried to work with ranking the DIFF Date values by a measure like the following:

Rank = RANKX(All(Gesamt[Datum]),CALCULATE(AVERAGE(Gesamt[DIFF Date])))

which give the right ranking if I show it in a table visual. 

My idea was to create another measure which includes a filter on "Rank=MAX-1". But as I know now, the filter in "Calculate" do only work with colums and not with measure (right?). 

Do you guys have any other idea to get that second latest value? I want to do calculations afterwards (Difference between newest and second latest) so a simple visual filter won't work unfotunately. 

Thank you in advance for your help, I really appreciate your time. 

Strueddi

[mh2587]

//Try this 
DifferenceSecondLatestLast = 
VAR LatestRank = RANKX(ALL(Gesamt), CALCULATE(AVERAGE(Gesamt[DIFF Date])))
VAR SecondLatestRank = LatestRank - 1

RETURN
CALCULATE(
    AVERAGE(Gesamt[Gewicht]),
    FILTER(ALL(Gesamt), Rank IN {LatestRank, SecondLatestRank})
) -
CALCULATE(
    AVERAGE(Gesamt[Gewicht]),
    Rank = LatestRank
)

[Anonymous]

Hi

if I try this, I got an error message (as in the picture). 

Even if I put the "Rank" in [ ] to define it's the measure,  it's not working. 

Also the "Latest Rank" does not define the latest rank, right? IS there a "Max" missig? 

Thanks 

[mh2587]

You can add visual filter it works with measures and put the rank measure value to 2

[Anonymous]

Thanks Muhammad for your reply. 

As I mentioned I want to calculate in another step with the second latest value (at the end it's the difference between the latest and the second latest). So only by filtering the visual it won't work. Or am I thinking in a wrong way?