Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

second latest value

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 CodeDatumGewicht

DIFFDate

Person 115.01.20248831
Person 116.01.20248930
Person 117.01.20248729
Person 118.01.202488.528
Person 119.01.202489.527
Person 120.01.202487.526
Person 221.01.202460.525
Person 222.01.20246024
Person 223.01.20246123
Person 224.01.202459.522
Person 215.01.20245931
Person 313.01.20247033
Person 314.01.20247132
Person 315.01.20246931
Person 316.01.202469.530
Person 317.01.202470.529
Person 318.01.202471.528
Person 324.01.20247222

 

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
Need Help
Tips and Tricks

4 Replies

  • mh2587's avatar
    mh2587
    Super User
    2 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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?

       

  • mh2587's avatar
    mh2587
    Super User
    2 years ago
    //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's avatar
      Anonymous
      Not applicable
      2 years ago

      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