Calculate Duration Column From Status's

[Anonymous]

Hello all,
I'm trying to figure out how to calculate the Duration of each status change from 'Old Value' to 'New Value' using the 'CreatedDate' column.

For every 'Case #', everytime the 'New Value' column changes to the next iteration of 'Old Value', subtract the 'CreatedDate' with the earlier 'CreatedDate' (sorted by ascending) and output it in hh:mm:ss format in the 'Status Duration' column. My ultimate goal is to create visualizations of how long the total number of Cases and each iteration of a Case, were in-progress, waiting on customer, waiting on QA, etc..., How would I go about doing this? Thank you for the help! 

[v-cazheng-msft]

Hi, @Anonymous 

You can create two Calculated columns to get the result you want.

Calculated column 1:

rank_table = RANKX('Table','Table'[CreatedDate],,ASC)

Calculated column 2:

Status Duration =

VAR res =

    MINX (

        FILTER (

            ALL ( 'Table' ),

            'Table'[rank_table]

                = EARLIER ( 'Table'[rank_table] ) + 1

        ),

        'Table'[CreatedDate]

    ) - 'Table'[CreatedDate]

RETURN

    IF ( 'Table'[NewValue] = "Closed", BLANK (), FORMAT ( res, "hh:mm:ss" ) )

The result looks like this:

Here is the pbix.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

thank you for reply! unfortunately this won't work fro me because there's many Cases that may have been done sequentially. ranking only by createdDate won't work. I need to find a way to filter by createdDate  AND caseID, is there a way to do that?

[v-cazheng-msft · Accepted solution]

Hi, @Anonymous 

You can make some changes to the dax formulas.

Calculated column 1:

rank_table = RANKX(FILTER(ALL('Table'),'Table'[CaseId]=EARLIER('Table'[CaseId])),'Table'[CreatedDate],,ASC)

Calculated column 2:

Status Duration =

VAR res =

    MINX (

        FILTER (

            ALL ( 'Table' ),

            'Table'[rank_table]

                = EARLIER ( 'Table'[rank_table] ) + 1&&'Table'[CaseId]=EARLIER('Table'[CaseId])

        ),

        'Table'[CreatedDate]

    ) - 'Table'[CreatedDate]

RETURN

    IF ( 'Table'[NewValue] = "Closed", BLANK (), FORMAT ( res, "hh:mm:ss" ) )

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try a new column like

new column =
var _oldvalue = [newvalue]
return
datediff(maxx(filter(Table, [CaseID] = earlier([Case ID]) && [newvalue] <> [oldvalue] && [newvalue] =_oldvalue && [CreatedDate] <earlier([CreatedDate])),[CreatedDate]),[CreatedDate],minute)

[Anonymous]

Thank you for the reply! Unfortunately this didn't work. After I isolated one case, and made the CreatedDate sorted as ascending, there is many blank spots under the new duration status column where I put your formula as you can see here: 
Case History Table Image