Measure to finde time difference between two ordered timestamps within one category

[charlineklapu]

Hi all,

data looks as followed:

Customer	Opened on	Case	Status	Timestamp		hh:mm:ss
712	18.11.2022 00:00	2100045	In Progress	18.11.2022 07:28
712	18.11.2022 00:00	2100045	Closed	18.11.2022 07:33
712	18.11.2022 00:00	2100146	In Queue	18.11.2022 07:40
712	19.11.2022 00:00	2103706	New	19.11.2022 07:37
712	19.11.2022 00:00	2103706	New	19.11.2022 07:37
712	19.11.2022 00:00	2103706	Request Processed	19.11.2022 07:37		00:04:00	<24h	n.FCR
712	18.11.2022 00:00	2100146	In Queue	19.11.2022 07:41
712	18.11.2022 00:00	2100146	In Queue	21.11.2022 07:44
712	18.11.2022 00:00	2100146	In Queue	21.11.2022 07:53
712	18.11.2022 00:00	2100146	In Progress	21.11.2022 07:53
712	18.11.2022 00:00	2100146	Request Processed	21.11.2022 07:57			>24h	FCR
124	18.11.2022 00:00	2100546	New	21.11.2022 17:00
124	18.11.2022 00:00	2100546	Request Processed	21.11.2022 17:57			>24h	FCR
234	18.11.2022 00:00	2100946	New	21.11.2022 17:00
234	18.11.2022 00:00	2100946	Request Processed	21.11.2022 17:57		01:00:00	<24h	n.FCR
234	18.11.2022 00:00	2100946	in Queue	21.11.2022 18:57
234	18.11.2022 00:00	2100946	Request Processed	24.11.2022 19:57			>24h	FCR

I want to look at customers and his/her cases (One customer can have one-many cases). Once a case reached the status "request processed" at time stamp column it can either be "FCR" or "n.FCR" (FCR standing for first call resolution). A case can reach "request processed" multiple times. 

FCR would mean that after the status "request processed" was reached, there were no further changes (no matter which status) to the case within 24 hours. 

Therefore at each status "request processed" I want to have a column indicating wether it is "FCR" or "n.FCR". Shown by example column on top in orange color.

Thank you for your help!

[charlineklapu]

Hi,

thanks for your answer. 

How do I set up an Index column while using direct query in an sql analysis server datasource? While using this mode and going into power query editor I see no queries.

Thanks for the help again!

[v-xiaosun-msft · Accepted solution]

Hi @charlineklapu .

According to your description, here is my solution, and please follow these steps.

Firstly, process the data and make sure that your columns "Opened on" and "Timestamp" are Date/time format.

And then use Power Query to add an index column.

And then create a calculated column.

Column =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Timestamp] ),
        FILTER ( 'Table', [Index] = EARLIER ( 'Table'[Index] ) + 1 )
    )
VAR _b =
    DATEDIFF ( _a, [Timestamp], HOUR )
VAR _c =
    CALCULATE (
        MIN ( 'Table'[Timestamp] ),
        FILTER (
            'Table',
            [Customer] = EARLIER ( 'Table'[Customer] )
                && [Timestamp] > EARLIER ( 'Table'[Timestamp] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        [Status] = "Request Processed"
            && ISBLANK ( _c ), "FCR",
        [Status] = "Request Processed"
            && _b > 24, "FCR",
        [Status] = "Request Processed"
            && _b < 24, "n.FCR"
    )

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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