Max funtion

[Jeffrey_VC]

Hello Community,

I'm a newbie who is interesting in Power Bi and the working of DAX.

Now I'm trying to get the penultimate record in a colum. Is there a function in DAX what can help me?

Please can anyone help me in achiving this measure.

Thanks,

Jeffrey

[colacan]

Hi, You can filter out the row which has minium value then get the minimum value to find penultimate as below.

I hope this helps you. 

Thanks

[Jeffrey_VC]

Hi,

Thank you for your answer.

I have tried:

Penultimate =

var minscore = minx(all('Ledigingen uit Middleware'[Datum/tijd]),

        'Ledigingen uit Middleware'[Datum/tijd])

var penul_wo_min = FILTER('Ledigingen uit Middleware',

     'Ledigingen uit Middleware'[Datum/tijd] <> minscore)

return MINX(penul_wo_min,'Ledigingen uit Middleware'[Datum/tijd])

The result is not the Penultimate but the first in the dataset:

And I need:

[johnt75 · Accepted solution]

If you have a unique column in the table, like an ID, then you could do something like

Penultimate value =
VAR maxID =
    MAX ( 'Table'[ID] )
VAR val =
    SELECTCOLUMNS (
        CALCULATETABLE ( TOPN ( 1, 'Table', 'Table'[ID] ), 'Table'[ID] < maxID ),
        "@val", 'Table'[Desired column]
    )
RETURN
    val

[Jeffrey_VC]

Hi Johnt75,

Yes that looks like a solution. Thnx!