Second Largest Value in Calculated Column

0
votes

I am looking to get the second last login for a user from the AccessLog Power BI Table. Using a calculated column to get the last login in the ApplicationUSer table, but also need the second to last login, in a calculated column, if it exists otherwise set to default date. ApplicationUser table is linked to the ApplicationAccessLog table via userid.

Lastest Access Date =
VAR Ho =
    CALCULATE ( MAX ( 'ApplicationAccessLog'[LoginDate] ) )
RETURN
    IF ( ISBLANK ( HO ), 0, HO )

What is the best way to get the second largest value?

The max login and max -1 login needs to be less than or equal to the selected date of my report page as well, so thinking now towards a measure now. If that Selected date is the 25th Dec, then the max login needs to be from the 25th Dec or before and the same with max login -1.

1
powerbidax

1 Answers

5
votes

There are a couple of functions you can rank values with, RANK.EQ / RANKX or TOPN.

Here's an attempt with the latter:

2ndToLast = MINX ( TOPN ( 2, 'Log', 'Log'[LoginDate] ), 'Log'[LoginDate] )

This returns the top two rows sorted by LoginDate and then selects the minimal LoginDate from those two rows that TOPN returns.