Forum Discussion
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 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!
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.
4 Replies
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.
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.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 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.
