Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate Duration Column From Status's

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...
Need Help
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
v-cazheng-msft
Community Support
5 years ago

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.