DATEDIFF between two tasks in same column

[Anonymous]

Hello,

I am fairly new to Power BI and DAX. I am trying to get a DATEDIFF between two tasks within the same column. There are other tasks in the workflow besides what I want to calculate. I was able to create a calculated column (Rework_Start_Finish) to get just the dates to calculate the rework times. The rework time needs to be calculated using the difference between the start time of the Rework Task and the start time of the Submit for Approval Task. The issue I am having is how to write the DAX to get the date difference.

Here is example data for 2 workflows from the Work_Item table. I have many thousands of them in the database that will need to be calculated

Workflow_ID	Task	Task Start Date	Rework_Start_Finish
1	Submit for Approval	12/2/2021 14:23
1	Process	12/2/2021 15:06
1	Rework	12/2/2021 15:32	12/2/2021 15:32
1	Submit for Approval	12/2/2021 16:49	12/2/2021 16:49
1	Process	12/2/2021 16:49
1	Review	12/2/2021 16:52
1	Rework	4/8/2022 18:20	4/8/2022 18:20
1	Submit for Approval	5/5/2022 13:43	5/5/2022 13:43
1	Process	5/6/2022 14:22
1	Rework	5/6/2022 14:34	5/6/2022 14:34
1	Submit for Approval	5/6/2022 14:54	5/6/2022 14:54
1	Process	5/6/2022 14:55
1	Review	5/6/2022 15:23
1	Rework	6/3/2022 13:31	6/3/2022 13:31
1	Submit for Approval	6/9/2022 19:44	6/9/2022 19:44
1	Process	6/9/2022 19:51
1	Review	6/9/2022 20:23
1	Rework	6/17/2022 14:13	6/17/2022 14:13
1	Submit for Approval	6/17/2022 15:29	6/17/2022 15:29
1	Process	6/17/2022 15:31
1	Rework	6/17/2022 15:49	6/17/2022 15:49
1	Submit for Approval	6/17/2022 16:49	6/17/2022 16:49
2	Submit for Approval	6/23/2022 14:28
2	Submit for Approval	6/23/2022 14:59
2	Rework	7/25/2022 21:58	7/25/2022 21:58
2	Submit for Approval	8/2/2022 14:08	8/2/2022 14:08
2	Process	8/2/2022 14:12
2	Rework	8/2/2022 14:45	8/2/2022 14:45
2	Submit for Approval	8/2/2022 15:48	8/2/2022 15:48
2	Process	8/2/2022 15:51
2	Review	8/2/2022 16:03
2	Rework	8/3/2022 14:11	8/3/2022 14:11
2	Submit for Approval	8/3/2022 14:59	8/3/2022 14:59
2	Process	8/3/2022 15:03
2	Review	8/3/2022 15:05

Here is the expected result after the DAX for the Rework Time in Minutes column

Workflow_ID	Task	Task Start Date	Rework_Start_Finish	Rework Time in Minutes
1	Submit for Approval	12/2/2021 14:23
1	Process	12/2/2021 15:06
1	Rework	12/2/2021 15:32	12/2/2021 15:32
1	Submit for Approval	12/2/2021 16:49	12/2/2021 16:49	76
1	Process	12/2/2021 16:49
1	Review	12/2/2021 16:52
1	Rework	4/8/2022 18:20	4/8/2022 18:20
1	Submit for Approval	5/5/2022 13:43	5/5/2022 13:43	38603
1	Process	5/6/2022 14:22
1	Rework	5/6/2022 14:34	5/6/2022 14:34
1	Submit for Approval	5/6/2022 14:54	5/6/2022 14:54	20
1	Process	5/6/2022 14:55
1	Review	5/6/2022 15:23
1	Rework	6/3/2022 13:31	6/3/2022 13:31
1	Submit for Approval	6/9/2022 19:44	6/9/2022 19:44	9014
1	Process	6/9/2022 19:51
1	Review	6/9/2022 20:23
1	Rework	6/17/2022 14:13	6/17/2022 14:13
1	Submit for Approval	6/17/2022 15:29	6/17/2022 15:29	76
1	Process	6/17/2022 15:31
1	Rework	6/17/2022 15:49	6/17/2022 15:49
1	Submit for Approval	6/17/2022 16:49	6/17/2022 16:49	60
2	Submit for Approval	6/23/2022 14:28
2	Submit for Approval	6/23/2022 14:59
2	Rework	7/25/2022 21:58	7/25/2022 21:58
2	Submit for Approval	8/2/2022 14:08	8/2/2022 14:08	11049
2	Process	8/2/2022 14:12
2	Rework	8/2/2022 14:45	8/2/2022 14:45
2	Submit for Approval	8/2/2022 15:48	8/2/2022 15:48	62
2	Process	8/2/2022 15:51
2	Review	8/2/2022 16:03
2	Rework	8/3/2022 14:11	8/3/2022 14:11
2	Submit for Approval	8/3/2022 14:59	8/3/2022 14:59	48
2	Process	8/3/2022 15:03
2	Review	8/3/2022 15:05

Here is the DAX I used to get the Rework_Start_Finish calculated column in the Work_Item:

Rework_Start_Finish =
VAR First_Rework_Task =
    CALCULATE (
        MINX ( Work_Item, Work_Item[WORK_CREATE] ),
        ALLEXCEPT ( Work_Item, Work_Item[Workflow_ID] ),
        Work_Item[TASK] = "Rework"
    )
RETURN
    IF (
        Work_Item[TASK] = "Rework"
            || Work_Item[TASK] = "Submit for Approval"
                && NOT ( ISBLANK ( First_Rework_Task ) )
                    && First_Rework_Task <= Work_Item[WORK_CREATE],
        Work_Item[WORK_CREATE]
    )

I would appreciate any help that anyone can provide me.

Thanks,

Dennis_W

[FreemanZ · Accepted solution]

hi @Anonymous 

try to write the column like this:

ReworkInMinutes = 
VAR _date = [Task Start Date] 
VAR _id = [Workflow_ID]
VAR _reworkdate = 
MAXX(
    FILTER(
        TableName,
        TableName[Task Start Date] <_date
            &&TableName[Workflow_ID] = _id
            &&TableName[Task] = "Rework"
    ),
    TableName[Task Start Date]
)
RETURN
IF(
    [Task]="Submit for Approval",
    DATEDIFF(_reworkdate, _date, MINUTE),
    BLANK()
)

it worked like this:

[Anonymous]

Thank you!! That worked.

Dennis_W