Forum Discussion
Calculate timeline period
Hi all,
I have a question abour a timeline calculation based on below table.
If the POSITION and the CTR_NR is the same and the TYPE is WAITING ESTIMATE or UNDER REPAIR, I want to calculate the time frame untill the next TIME_STAMP.
For example:
MNBU3337892 is on position WP4.19.
The TIME_STAMP of WAITING ESTIMATE is 26-10-2020 07:00.
The next timestamp after WAITING ESTIMATE is REPAIR COMPLETE on 26-10-2020 09:00
So, the outcome of the calculation is 2 hours.
I hope someone can help me with this!
John
| position | ctr_nr | type | time_stamp | time_period (hrs) |
| WP4.19 | MNBU3447892 | Waiting Estimate | 26-10-2020 07:00 | 2 |
| WP4.19 | MNBU3447892 | Repair Complete | 26-10-2020 09:00 | |
| WP4.19 | SUDU8068773 | Waiting Estimate | 26-10-2020 22:00 | 6 |
| WP4.19 | SUDU8068773 | Awaiting Authorisation | 27-10-2020 04:00 | |
| WP4.19 | HLCU9852359 | Under Repair | 29-10-2020 11:00 | 3 |
| WP4.19 | HLCU9852359 | Repair Complete | 29-10-2020 14:00 |
Hey Anonymous ,
I'm wondering if there is given order of types. Let's say
1. Waiting Estimate
2. Under Repair
3. ...
Regards,
Tom
Anonymous
you can try to create a column
Column = VAR _time=minx(FILTER('Table','Table'[ctr_nr]=EARLIER('Table'[ctr_nr])&&'Table'[time_stamp]>EARLIER('Table'[time_stamp])),'Table'[time_stamp]) return if('Table'[type]="Waiting Estimate"||'Table'[type]="Under Repair",DATEDIFF('Table'[time_stamp],_time,HOUR),BLANK())
6 Replies
- TomMartens
Super User
Hey Anonymous ,
I'm wondering if there is given order of types. Let's say
1. Waiting Estimate
2. Under Repair
3. ...
Regards,
Tom
TomMartens good day,
There is indeed a given order:
Waiting Estimate can result in either repair complete or waiting estimate.
Under repair can result in repair complete
Thanks,
John
- TomMartens
Hey Anonymous ,
wondering how "Awaiting authorization" fits into this?
Is there a typo or can there be more "Waiting estimates" before it becomes "Repair complete".
Regards,
Tom
ryan_mayuAnonymous
you can try to create a column
Column = VAR _time=minx(FILTER('Table','Table'[ctr_nr]=EARLIER('Table'[ctr_nr])&&'Table'[time_stamp]>EARLIER('Table'[time_stamp])),'Table'[time_stamp]) return if('Table'[type]="Waiting Estimate"||'Table'[type]="Under Repair",DATEDIFF('Table'[time_stamp],_time,HOUR),BLANK())