Conditional date difference

[LuciferMstar]

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 columns, for each ID, and depending on the condition to calculate the DateDiff.

The logic would be like this: If [Status 2] > [Status 1] and [Status 2] < [Status 3] then [Status 3] - [Status 2].

ID	Status 1	Status 2	Status 3
1	18/01/2022
1	15/02/2022	02/02/2022	06/04/2022
1		23/03/2022
2		25/04/2023	29/04/2023
2	15/04/2023	27/04/2023	10/04/2023

The output would be like this:

ID	Days
1	78
1	14
2	4
2	2

Thank you!

[v-xinruzhu-msft]

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]

Hi @v-xinruzhu-msft ,

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. 

ID	Status Name	Status Created Date	Status 1	Status 2	Status 3	Days
5	Status 2	03/01/2022		03/01/2022		346
5	Status 1	10/01/2022	10/01/2022
5	Status 3	18/02/2022			18/02/2022
5	Status 3	24/02/2022			24/02/2022
5	Status 2	25/02/2022		25/02/2022		143
5	Status 3	18/07/2022			18/07/2022
5	Status 3	15/08/2022			15/08/2022
5	Status 3	18/08/2022			18/08/2022
5	Status 3	20/09/2022			20/09/2022
5	Status 1	10/10/2022	10/10/2022
5	Status 2	25/10/2022		25/10/2022		51
5	Status 3	18/11/2022			18/11/2022
5	Status 3	15/12/2022			15/12/2022

[v-xinruzhu-msft · Accepted solution]

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]

Hi @v-xinruzhu-msft ,

Many thanks! It works!