How to get the length of time between the different status

[Anonymous]

Hi

I have a dataset contain Reference Number , the Inquiry Status and Timestamp.

These are the list of Inquiry History, and their data key is the combination of Reference number and Status.

I want to get how long time dose it take to change to the next status. 

For example

 InquiryNo:0001  Status:Created          TimeStamp:2019/01/05 12:00:00

 InquiryNo:0001  Status:Resolved        TimeStamp:2019/01/06 08:00:00

  - I want to get the result  "20 hours".

    It looks so easy.

    But in fact, these have more than 3 status in one Reference number.

Please tell me how to link these different status to calculate the length.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Before any transformation, go to Edit queries, Add a index column from 1.

Then close &&apply to Report view, create such measures

last status =
CALCULATE (
    MAX ( Sheet1[Status] ),
    FILTER (
        ALL ( Sheet1 ),
        Sheet1[InquiryNo] = MAX ( Sheet1[InquiryNo] )
            && Sheet1[Index]
                = MAX ( Sheet1[Index] ) - 1
    )
)


context = [last status]&"~"&MAX(Sheet1[Status])

last_time =
CALCULATE (
    SUM ( Sheet1[TimeStamp] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[InquiryNo] ),
        Sheet1[Index]
            = MAX ( Sheet1[Index] ) - 1
    )
)

diff_hour = IF(NOT(ISBLANK([last status])),DATEDIFF([last_time],MAX(Sheet1[TimeStamp]),HOUR))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Maggie!

Thanks for your great solution.

This may be the best way to solve this problem!

I'll try this!

If I have any question,I'll ask it here again.

I appreciate your support.

Best Regard.

[v-juanli-msft]

Hi @Anonymous 

How many is the max times o status?

If it is not very big, let to say <=5, you could try Power Query to transform and calculate this problem.

Please let me know so that i can work with the proper way for your problem.

Best Regards
Maggie

[Anonymous]

Hi! Maggie Thanks for your quick response. We have 6 kinds of status. "Created" is the first status, and "Resolved" is the last status. But other values are not chronology. Example This flow means chronology time chart of the inquiry status. Created -> commented -> file uploaded -> commented -> Resolved And I want to get the each length of time between "Created" and "commented","commented"and "file uploaded" and other pairs Best Regard.

[v-juanli-msft]

Hi @Anonymous 

Created -> commented -> file uploaded -> commented -> Resolved 

There are two "commented" status in a process for one InquiryNo, are they the same one or different one?

You say 6 kinds of status, are the 6 status all different?

or you mean for a InquiryNo, it has <=6 status, some status may be the same?

In the example above, there are 4 kinds of status or 5?

Best Regards
Maggie

[Anonymous]

Hi! Maggie

These two "commented" means same status.

(I used that sample to explain these values don't transit chronology)

And  "6 kinds" means that the 6 status all different.

Best Regard.