cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Helper III

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))```
5 REPLIES 5
Helper III

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
Not applicable

@Sweet-T

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

Regards

Resolver I

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

Helper III

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

Helper III

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

Announcements