Forum Discussion

LuciferMstar's avatar
LuciferMstar
Helper 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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi LuciferMstar 

You can refer to the following calculated column

Days =
VAR _loopup1 =
    FILTER ( 'Table', [ID] = EARLIER ( 'Table'[ID] ) )
VAR _status1 =
    MAXX ( _loopup1, [Status 1] )
VAR _status2 =
    MAXX ( _loopup1, [Status 2] )
VAR _status3 =
    MAXX ( _loopup1, [Status 3] )
RETURN
    IF (
        _status1 < _status2
            && _status2 < _status3,
        IF ( [Status 2] <> BLANK (), DATEDIFF ( [Status 2], _status3, DAY ) )
    )

 

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
    Helper 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
        Helper I
        3 years ago

        Hi Anonymous ,

         

        Many thanks! It works!