Forum Discussion

LuciferMstar's avatar
LuciferMstar
Icon for Helper I rankHelper I
3 years ago
Solved

Conditional date difference

Hello,   I'm struggling with some comparisons and date difference when I have this kind of data in which some cells are blanks. What do I need to do here is to compare each value in the specified ...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi LuciferMstar 

    You can try the following column

    Days =
VAR a =
    FILTER ( 'Table', [ID] = EARLIER ( 'Table'[ID] ) )
VAR b =
    ADDCOLUMNS (
        SUMMARIZE ( FILTER ( a, [Status 1] <> BLANK () ), [Status 1] ),
        "Status 2", MINX ( FILTER ( a, [Status 2] > EARLIER ( 'Table'[Status 1] ) ), [Status 2] )
    )
VAR c =
    ADDCOLUMNS (
        b,
        "Status 3", MINX ( FILTER ( a, [Status 3] > EARLIER ( [Status 2] ) ), [Status 3] )
    )
VAR d =
    ADDCOLUMNS ( c, "Dateiff", DATEDIFF ( [Status 2], [Status 3], DAY ) )
RETURN
    MAXX ( FILTER ( d, [Status 2] = EARLIER ( 'Table'[Status 2] ) ), [Dateiff] )

    and the result you have offered the dateiff of 

    25/10/2022 18/11/2022  

    should be 24

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

LuciferMstar's avatar
LuciferMstar
Icon for Helper I rankHelper I
3 years ago

Hi Anonymous ,

 

Thank you, in some cases it works, but in other cases it doesn't because of MAXX.

 

In the following example it needs to ignore the first row because the first Status 2 date falls below of the first Status 1 date, and should perform the first DATEDIFF only on the first Status 3 date that comes only after the second Status 2 date. 


Now, the thing is that I might have another set of Status 1, Status 2 and Status 3 and that would mean a second DATEDIFF. 

IDStatus NameStatus Created DateStatus 1Status 2Status 3Days
5Status 203/01/2022 03/01/2022 346
5Status 110/01/202210/01/2022   
5Status 318/02/2022  18/02/2022 
5Status 324/02/2022  24/02/2022 
5Status 225/02/2022 25/02/2022 143
5Status 318/07/2022  18/07/2022 
5Status 315/08/2022  15/08/2022 
5Status 318/08/2022  18/08/2022 
5Status 320/09/2022  20/09/2022 
5Status 110/10/202210/10/2022   
5Status 225/10/2022 25/10/2022 51
5Status 318/11/2022  18/11/2022 
5Status 315/12/2022  15/12/2022 
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi LuciferMstar 

You can try the following column

Days =
VAR a =
    FILTER ( 'Table', [ID] = EARLIER ( 'Table'[ID] ) )
VAR b =
    ADDCOLUMNS (
        SUMMARIZE ( FILTER ( a, [Status 1] <> BLANK () ), [Status 1] ),
        "Status 2", MINX ( FILTER ( a, [Status 2] > EARLIER ( 'Table'[Status 1] ) ), [Status 2] )
    )
VAR c =
    ADDCOLUMNS (
        b,
        "Status 3", MINX ( FILTER ( a, [Status 3] > EARLIER ( [Status 2] ) ), [Status 3] )
    )
VAR d =
    ADDCOLUMNS ( c, "Dateiff", DATEDIFF ( [Status 2], [Status 3], DAY ) )
RETURN
    MAXX ( FILTER ( d, [Status 2] = EARLIER ( 'Table'[Status 2] ) ), [Dateiff] )

and the result you have offered the dateiff of 

25/10/2022 18/11/2022  

should be 24

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • LuciferMstar's avatar
    LuciferMstar
    Icon for Helper I rankHelper I
    3 years ago

    Hi Anonymous ,

     

    Many thanks! It works!