Calculate timeline period

[jwi1]

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

[TomMartens · Accepted solution]

Hey @jwi1 ,

I'm wondering if there is given order of types. Let's say

1. Waiting Estimate

2. Under Repair

3. ...

Regards,

Tom

[ryan_mayu · Accepted solution]

@jwi1 

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())

[TomMartens · Accepted solution]

Hey @jwi1 ,

I'm wondering if there is given order of types. Let's say

1. Waiting Estimate

2. Under Repair

3. ...

Regards,

Tom

[jwi1]

@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 @jwi1 ,

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 

[jwi1]

Hi @TomMartens ,

Sorry, typo error....😅

Please read:

Waiting Estimate can result in either Repair Complete or Awaiting Authorisation.

Under repair can result in Repair Complete

John

[v-jingzhang]

Hi @jwi1 

You could try to add a new column with the following DAX code:

Hours =
IF (
    'Table'[type] IN { "Waiting Estimate", "Under Repair" },
    DATEDIFF (
        'Table'[time_stamp],
        MINX (
            FILTER (
                'Table',
                'Table'[position] = EARLIER ( 'Table'[position] )
                    && 'Table'[ctr_nr] = EARLIER ( 'Table'[ctr_nr] )
                    && 'Table'[time_stamp] > EARLIER ( 'Table'[time_stamp] )
            ),
            'Table'[time_stamp]
        ),
        HOUR
    ),
    BLANK ()
)

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.