How can i get the second highest ou lowest value?

[Anonymous]

Hi,

I have a process that returns several date/time values for tha same control number. For example:

row     number   date/time

1         1234        24/08/2020 10:12

2         1234        24/08/2020 11:25

3         1234        24/08/2020 11:48

4         1234        24/08/2020 12:36

I need a measure that recognizes the oldest and the econd oldest value for the same control number and calculate the diferrence between then. In this case they are in the rows number 1 (oldest) and 2 (second oldest) and the diference is 01:13 (it can be in minutes too, 01:13 = 73 minutes).

[Fowmy]

@Anonymous 


Try this measure please:

Difference = 
VAR _MIN = 
    CALCULATE(
        MIN(CONTROL[Date]),
        ALLEXCEPT(CONTROL,CONTROL[Number])
    )

VAR _MIN2 = 
    CALCULATE(
        MIN(CONTROL[Date]),
        CONTROL[Date] > _MIN,
        ALLEXCEPT(CONTROL,CONTROL[Number]))    
RETURN
DATEDIFF(
    _MIN,
    _MIN2,
    MINUTE
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[ryan_mayu · Accepted solution]

@Anonymous 

Please try this

Measure = 
VAR _MIN=MIN('Table'[date/time])
VAR _SECMIN=MINX(FILTER('Table','Table'[date/time]>_MIN),'Table'[date/time])
RETURN DATEDIFF(_MIN,_SECMIN,MINUTE)

[amitchandak]

@Anonymous , Try like

datediff(maxx(filter(table,[number] =earlier([number]) && [row] =earlier([row])-1 ),[date/time]), [date/time],minute)

Assumption row number continous