Forum Discussion
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 '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!
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.
5 Replies
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)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
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.
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.
