DATEDIFF: finding column with most recent date

v-huizhn-msft
Sure Angelia, sorry I did a bad job of explaining. 
Not all jobs move in a linear fashion; some will start in Stage 2 (or 3, 4, etc.) and move backwards before forwards. 

Example: Job C
Starts in Stage 2 (January 10th, 2018)
Moves to Stage 1 (Feb 14, 2018)

Progresses to Stage 3 (March 12, 2018)

then Stage 4 (Apr 10, 2018)

and finally, Stage 5 (May 22, 2018). 

This means the time spent in each Stage is: 

Stage 2: 35 days (January 10th to Feb 14)

Stage 1: 26 days (Feb 14th to March 12)

Stage 3: 29 days (March 12 to April 10)

Stage 4: 42 days (April 10 to May 22). 

I would like to account for all of these scenarios by creating a calculated column to determine how long each job spent in that stage, before moving on to the next Stage (whether that stage is directly after, 1 -> 2, or not, 3 ->1). Essentially I need to evaluate the 5 columns on a row-by-row basis to determine which column contains the NEXT smallest date, and then use that date for in DATEDIFF. 

My current brute-force approach involves determining all permutations that may contain the "next smallest" date, and writing a variable to calculate the minimum of these: 

DateMin(2,3,4,5) = 
VAR AllDates = {
    ([Entered 2]),
    ([Entered 3]),
    ([Entered 4]),
    ([Entered 5])
     }
    VAR MinDate = 
        MINX ( FILTER( AllDates, [Value] <> 0), [Value] )
    RETURN
        MINX( FILTER( AllDates, [Value] = MinDate ), [Value] )

And then after I have determined all those, writing a massive IF statement. I've only done it for the first Stage (below) so far, and it wasn't fun. There must be a more efficient way!

Velocity: 1 = 
// First check if the job has completed. If so, compute time between start and finish
IF([Completed Date] <> 0, DATEDIFF([Entered 1], [Completed Date], DAY), 
//Otherwise, check all permutations until position is found 
IF([Entered 1] < [DateMin(2,3,4,5)], DATEDIFF([Entered 1], [DateMin(2,3,4,5)], DAY), 
        IF([Entered 1] < [DateMin(2,3,4)], DATEDIFF([Entered 1], [DateMin(2,3,4)], DAY), 
            IF([Entered 1] < [DateMin(2,3,5)], DATEDIFF([Entered 1], [DateMin(2,3,5)], DAY),
                IF([Entered 1] < [DateMin(2,4,5)], DATEDIFF([Entered 1], [DateMin(2,4,5)], DAY), 
                    IF([Entered 1] < [DateMin(3,4,5)], DATEDIFF([Entered 1], [DateMin(3,4,5)], DAY), 
                        IF([Entered 1] < [DateMin(2,3)], DATEDIFF([Entered 1], [DateMin(2,3)], DAY),
                            IF([Entered 1] < [DateMin(2,4)], DATEDIFF([Entered 1], [DateMin(2,4)], DAY),
                                IF([Entered 1] < [DateMin(2,5)], DATEDIFF([Entered 1], [DateMin(2,5)], DAY),
                                    IF([Entered 1] < [DateMin(3,4)], DATEDIFF([Entered SQ], [DateMin(3,4)], DAY),
                                        IF([Entered 1] < [DateMin(3,5)], DATEDIFF([Entered 1], [DateMin(3,5)], DAY),
                                            IF([Entered 1] < [DateMin(4,5)], DATEDIFF([Entered 1], [DateMin(4,5)], DAY), 
                                                //If not any of the above, the job is still in Stage 1
DATEDIFF([Entered 1], TODAY(), DAY)
                 
                           )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)