Find Earliest Date from Several Columns

[Sweet-T]

Hi everyone, 

I have 5 columns, each with various timestamps. I would like to poll each of the rows to find which column contains the earliest date in each row, and then calculate the time between that date and today. My attempt is below, but does not seem to be working!

 DATEDIFF( 
                         (SWITCH(TRUE(), 
                             [Entered Stage 1] < [Entered Stage 2], [Entered Stage 1],
                             [Entered Stage 2]< [Entered Stage 3], [Entered Stage 2],
                             [Entered Stage 3] < [Entered Stage 4], [Entered Stage 3],
                             [Entered Stage 4] < [Entered Stage 5], [Entered Stage 4],
                             [Entered Stage 5])), 
                             TODAY(), DAY )

Many thanks!

[Sweet-T · Accepted solution]

Figured it out!

For all those curious, first I determine the earliest date using: 

Date Entered Funnel = 
VAR AllDates = {
    ([Entered Stage 1]),
    ([Entered Stage 2]),
    ([Entered Stage 3]),
    ([Entered Stage 4]),
    ([Entered Stage 5]),
    ([Entered Stage 6]) }
    VAR MinDate = 
        MINX ( FILTER( AllDates, [Value] <> 0), [Value] )
    RETURN
        MINX( FILTER( AllDates, [Value] = MinDate ), [Value] )

Then I calculated the date between the date reported above, and today's date (unless the Job had completed, which I checked first). 

VelocityTotal All = 
//This function calculates the total velocity for each opportunity.
//It checks if the opportunity has completed first. If so, it reports the time between entering the funnel and completion date.
//If not, it takes the differece between entering the funnel and the current date, and reports it.  
IF(NOT(ISBLANK([Completed Date])), DATEDIFF([Date Entered Funnel], [Completed Date], DAY), 
    DATEDIFF([Date Entered Funnel], TODAY(), DAY))

[Anonymous]

@Sweet-T

Can you share your dataset (or a sample of your dataset) too so that it is easy to understand 

Regards

[dtartaglia]

Hi @Sweet-T

I'm glad you explained, I was trying to figure it out your issue but your original code was confusing and I didn't see how it could work on it's own (with or without blanks).

Dan

[Sweet-T]

@dtartaglia
No problem. I'll walk though what I intended, since that function has proved quite useful in the past. 

The basic syntax is: 
SWITCH (TRUE (), 
if , then, 
else if, then, 
else if, then, 
else if, then, 

...., ...., 
else)

So my function checked each Stage to see if the date was earlier than the following Stage ( [Stage 1] < [Stage 2]). If so, it reported the earlier stage. If not, it moved to the next 2 Stages and evaluted them. But if there was an instance where [Stage X] = 0, it couldn't evaluate the expression and returned a null. 

Does that help? 

[Sweet-T]

Follow up to this: I'm pretty sure my issue is due to some of the date fields being blank, so the above function cannot check if [Stage 1] > [BLANK]. 

Any ideas how to rectify this? 

T