Forum Discussion
Calculate leadtime from a log file
Hi guys
I have a log fle that look something like this
| Project | Project Status | Project Status Entry Timestamp |
| 23-272 | 1.1 | 24.09.2023 19:30 |
| 23-272 | 1.2 | 24.09.2023 19:51 |
| 22-132 | 1.1 | 26.09.2023 14:06 |
| 22-132 | 1.2 | 29.09.2023 11:55 |
| 22-132 | 1.3 | 03.10.2023 07:39 |
| 23-272 | 1.3 | 04.10.2023 12:44 |
| 22-132 | 2.1 | 18.10.2023 13:29 |
| 22-132 | 2.2 | 01.11.2023 08:36 |
| 23-307 | 1.1 | 06.11.2023 08:14 |
| 23-307 | 1.2 | 06.11.2023 13:24 |
| 22-132 | 2.6 | 07.11.2023 18:31 |
| 23-307 | 1.3 | 15.11.2023 19:16 |
Now I need to calculate the leadtime for each step for each project, like shown in the example below
Where ever there is a NULL just means that the project is not finished with its current state. The NULL should be repleaced with leadtime from start of the state to todays date.
| Project | Project Status | Project Status Entry Timestamp | leadtime |
| 23-272 | 1.1 | 24.09.2023 19:30 | 0:20:56 |
| 23-272 | 1.2 | 24.09.2023 19:51 | 16:53:19 |
| 22-132 | 1.1 | 26.09.2023 14:06 | 21:49:18 |
| 22-132 | 1.2 | 29.09.2023 11:55 | 19:43:41 |
| 22-132 | 1.3 | 03.10.2023 07:39 | 5:50:28 |
| 23-272 | 1.3 | 04.10.2023 12:44 | NULL |
| 22-132 | 2.1 | 18.10.2023 13:29 | 19:07:11 |
| 22-132 | 2.2 | 01.11.2023 08:36 | 9:55:02 |
| 23-307 | 1.1 | 06.11.2023 08:14 | 5:09:40 |
| 23-307 | 1.2 | 06.11.2023 13:24 | 5:52:07 |
| 22-132 | 2.6 | 07.11.2023 18:31 | NULL |
| 23-307 | 1.3 | 15.11.2023 19:16 | NULL |
Is this possible to solve in a not to messy manner or do I need to put this thru same processing in our data modeling tool before Power BI can do its magic?
Thanks for your help
Best regards
Dan
8 Replies
Hi h4_dkj
What is the logic of calculating leadtime? Based on the information you have offed, I cannot understand the logic of the leadtime,should it have a start time of each status? can you offer some information about the logic of calculating lead time?
Best Regards!
Yolo Zhu
Hi
I have sorted the example table below make it more easy to explain
Every project gets a statdate when entering state 1.1 and a new date when entering state 1.2
The leadtime fot state 1.1 for a project is then the difference between the dates in 1.1 and 1.2
The leadtime fot state 1.2 for a project is then the difference between the dates in 1.2 and 1.3
and so on
If the project has no next known state to calcluate leadtime then todays date should be uses
like project 23-272 and 23-307 who both has last know state 1.3 then leadtime is the difference betweem coresponding date for thate state and todaysdate
Hope this made it easier ?
Project Project Status Project Status Entry Timestamp 22-132 1.1 26.09.2023 14:06 22-132 1.2 29.09.2023 11:55 22-132 1.3 03.10.2023 07:39 22-132 2.2 01.11.2023 08:36 22-132 2.1 18.10.2023 13:29 22-132 2.6 07.11.2023 18:31 23-272 1.1 24.09.2023 19:30 23-272 1.2 24.09.2023 19:51 23-272 1.3 04.10.2023 12:44 23-307 1.1 06.11.2023 08:14 23-307 1.2 06.11.2023 13:24 23-307 1.3 15.11.2023 19:16 Please clarify what your interpretation of work days and business hours is.
Your sample data seems to be missing the seconds and your expected outcome seems to be missing the days. Please clarify. Please also indicate how to handle weekends, holidays, and business hours.
Sorry for that.
It shoud only adopt to business hours and all lead time should be in hours and minutes
