Forum Discussion

Sweet-T's avatar
Sweet-T
Helper III
8 years ago

DATEDIFF: finding column with most recent date

Happy Monday everyone,

 

I'm trying to calculate the time (in days) it takes for a job to progress through my sales funnel. I have timestamps for each stage. However, some jobs enter at different stages, and can move backwards before moving fowards. This is creating negative numbers in my Calculated Column.

 

I think the following function may work, but currently it is returning nothing. I believe because any "null" values break the function (see Job B Stage 2, below):

Velocity: Stage 1 = 
DATEDIFF([Entered Stage 1], (MIN(MIN(MIN(MIN([Entered Stage 2],[Entered Stage 3]), [Entered Stage 4]), [Entered Stage 5]), [Entered Stage 6]))), DAY)

Also, the function had to be re-worked for jobs that moved backwards. This is what I was thinking, but welcome suggestions:

Velocity: Stage 2 = 
DATEDIFF([Entered Stage 2], 
    IF([Entered Stage 2] < [Entered Stage 1], [Entered Stage 1], 
        MIN(MIN(MIN([Entered Stage 3],[Entered Stage 4]), [Entered Stage 5]), [Entered Stage 6])), DAY)

 

Sample Data: 

JobStage 1Stage 2Stage 3Stage 4Stage 5 (complete)
AJanuary 10, 2018February 14, 2018March 12, 2018April 10, 2018May 22, 2018
BJanuary 10, 2018 February 14, 2018March 12, 2018April 10, 2018
CFebruary 14, 2018January 10, 2018March 12, 2018April 10, 2018May 22, 2018
DFebruary 14, 2018April 10, 2018January 10, 2018March 12, 2018May 22, 2018

 

Hopefull Result:

JobTime in Stage 1Time in Stage 2Time in Stage 3Time in Stage 4
A35262942
B250 or null2629
C26352942
D26423529

 

Any help would be appreciated. Thanks!

T

Need Help

3 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    Hi Sweet-T,

    In your "Hopefull Result", how do you get 25 for job B, and 26 for job C and D? Could you please list the logical thinking?

    And if you get negative number, you can use ABS function to get the positive number, you can use the following formula based on my understanding.

    Time in Stage1 =
ABS (
    IF (
        ISBLANK ( Table1[Stage 1] ),
        0,
        DATEDIFF (
            Table1[Stage 1],
            MIN (
                MIN ( MIN ( Table1[Stage 2], Table1[Stage 3] ), Table1[Stage 4] ),
                Table1[Stage 5 (complete)]
            ),
            DAY
        )
    )
)


    Best Regards,
    Angelia

    • Sweet-T's avatar
      Sweet-T
      Helper III
      8 years ago

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





      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        Hi Sweet-T,

        I understand your scenario now. While there is no permanent logic to find the minimum value at a time. Recursion for is unsupported  using DAX, we have to check all permutations until position is found. So it's hard to find a efficient way. PQ M language can deal with the recursion, you can post your thread to specific forum to get more dedicated support.

        Thanks,
        Angelia