cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Find Earliest Date from Several Columns

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper III
Helper III

Re: Find Earliest Date from Several Columns

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))

View solution in original post

5 REPLIES 5
Highlighted
Helper III
Helper III

Re: Find Earliest Date from Several Columns

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

Highlighted
Helper III
Helper III

Re: Find Earliest Date from Several Columns

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))

View solution in original post

Highlighted
Resolver I
Resolver I

Re: Find Earliest Date from Several Columns

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

Highlighted
Skilled Sharer
Skilled Sharer

Re: Find Earliest Date from Several Columns

@Sweet-T

 

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

 

Regards

Highlighted
Helper III
Helper III

Re: Find Earliest Date from Several Columns

@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? 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors