Rank Second Largest Criteria - Calculating Values

[Mic]

Dear Community,

i'm pretty fresh and new with Power Bi and already now confronted with a task i'm very much struggeling with.

already spend multiple hours analyzing this forum but did not find the fitting solution - hope s.b. in here will be able to guide me / sharing a solution.

Let me introducte to you the topic:

I do have a file containing 120k+ line items with ~40 columns.

Most important is, table is created by appending multiple single files from a folder, each of single file contains a column called "extract_date".

Format of this column is like: yyyymmdd, recognized as "number"

Looking into that column, now i need to get out for latest extract (my idea - highest number = formula 'max') the total of a column called "CashOut".

by using this formula (see below) i was already able to get it for latest data extract:

Total CashOut ACT = 
VAR vMax =
    MAX('01_Liqui_GP_View_fin'[Extract_Date]
    )
Return
    CALCULATE(
       SUM('01_Liqui_GP_View_fin'[FX EUR Due]), Filter('01_Liqui_GP_View_fin', vMax)
       )

Now it is about the same calculation but for second highest value - issue with this: i'm currently struggling to find a method getting this "secong out...

Anybody who might be able to help me solving this?

bR in advance

Mic

[Mic]

Hi Nickolaj,

back again and with feedback - unfortunately i was not able to get this transfered to my file, as i have some issues with the format of data - means additional "format changes" failed, due to the way i receive them.

BUT - while digesting on that i found out a function, i was not aware of before, which is the "index column" funtion. 

While processing the combination of data and transformation, i entered an index in first steps to define oldest dataset with index "0" and newest with 3 - so previous dataset will be defined by index 2.

Then calculation seems to work - this is only in review now.

Nevertheless, many thanks for your feedback and support.

best 

Mic

[NickolajJessen]

and i just realized that's not what you asked for, haha.

Here is for the second highest value

[NickolajJessen]

Hi, you could do something like this to get the second max date:

[Mic]

Hi Nickolaj,

first many thanks for your reply - i will check this on short note and will let you know if i was able to bring this into my perspective.

bR Mic