Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My calculation is looking at the completion time compared to the start of the service order but I need it to look at the prior completion time. So comp time for Task Sequence 2 - Comp Time for Task Sequence 1 = Time elapsed for Sequence 2.
The calculation below is returning the difference between the min and max times within a service order. I need to replace the min time with the max time of the previous sequence number. My problem is that I'm not great at focusing my calculation to a sequencial process.
Solved! Go to Solution.
Hi, @GunnerJ
Based on your description, i create data to reproduce your scenario. You can create a calculated column in data table as follows:
TASK TIME =
VAR seq = Workflow[BI_WRKFLW_TASK_SEQ_NBR]
VAR minDT =
CALCULATE (
Max ( Workflow[BI_EVENT_DT_TM] ),
FILTER (
Workflow ,
Workflow[BI_WRKFLW_TASK_SEQ_NBR]
= CALCULATE (
MAX ( Workflow[BI_WRKFLW_TASK_SEQ_NBR] ),
FILTER ( Workflow , Workflow[BI_WRKFLW_TASK_SEQ_NBR] < seq && Workflow[BI_SO_NBR] = EARLIER ( Workflow[BI_SO_NBR] )
&& Workflow[BI_TASK_CD] = EARLIER ( Workflow[BI_TASK_CD] )
&& Workflow[BI_WORK_EVENT_CD] = "COMP")
)
)
)
return
CALCULATE (
DATEDIFF (
minDT,
MAX ( Workflow[BI_EVENT_DT_TM] ),
SECOND
),
FILTER (
ALL ( Workflow ),
Workflow[BI_WRKFLW_TASK_SEQ_NBR] = EARLIER ( Workflow[BI_WRKFLW_TASK_SEQ_NBR] )
&& Workflow[BI_SO_NBR] = EARLIER ( Workflow[BI_SO_NBR] )
&& Workflow[BI_TASK_CD] = EARLIER ( Workflow[BI_TASK_CD] )
&& Workflow[BI_WORK_EVENT_CD] = "COMP"
)
)
Result:
If I misunderstand the your thoughts, please show us the sample data and expected output. I am glad to solve the problem for you.
Best Regards,
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @GunnerJ
If you take the answer of someone, please mark it as the solution to help the other members find it more quickly. If not, let me know and I'll try to help you further.
Thanks.
Best Regards,
Allan
Hi, @GunnerJ
Based on your description, i create data to reproduce your scenario. You can create a calculated column in data table as follows:
TASK TIME =
VAR seq = Workflow[BI_WRKFLW_TASK_SEQ_NBR]
VAR minDT =
CALCULATE (
Max ( Workflow[BI_EVENT_DT_TM] ),
FILTER (
Workflow ,
Workflow[BI_WRKFLW_TASK_SEQ_NBR]
= CALCULATE (
MAX ( Workflow[BI_WRKFLW_TASK_SEQ_NBR] ),
FILTER ( Workflow , Workflow[BI_WRKFLW_TASK_SEQ_NBR] < seq && Workflow[BI_SO_NBR] = EARLIER ( Workflow[BI_SO_NBR] )
&& Workflow[BI_TASK_CD] = EARLIER ( Workflow[BI_TASK_CD] )
&& Workflow[BI_WORK_EVENT_CD] = "COMP")
)
)
)
return
CALCULATE (
DATEDIFF (
minDT,
MAX ( Workflow[BI_EVENT_DT_TM] ),
SECOND
),
FILTER (
ALL ( Workflow ),
Workflow[BI_WRKFLW_TASK_SEQ_NBR] = EARLIER ( Workflow[BI_WRKFLW_TASK_SEQ_NBR] )
&& Workflow[BI_SO_NBR] = EARLIER ( Workflow[BI_SO_NBR] )
&& Workflow[BI_TASK_CD] = EARLIER ( Workflow[BI_TASK_CD] )
&& Workflow[BI_WORK_EVENT_CD] = "COMP"
)
)
Result:
If I misunderstand the your thoughts, please show us the sample data and expected output. I am glad to solve the problem for you.
Best Regards,
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The logic you are asking for is faulty on task 5 and 6. The second line in both tasks is not in chronological order and the difference between them would be a negative number. What would you want the result to be in that case? And what do you want the result to be in the base of a 'first occurence' of a task (so rownumber 1, 3, 5, 7 etc)?
I am thinking something allong the lines of if the task number occurence is the first (ranked by EVENT_DT_TM) then return something, else calculate the datediff between current row and the row where tasknumber < currentRowTasknumber and date = MAX(date).
Would love to figure this one out for you, could you please copy paste the table structure and sample data as tekst into a reply? Then I could copy it into power bi and create a solution for you (provided you can anser the first questions 🙂 ).
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |