cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate the no of days for a task which was spent in each state.

Hi All,

I would like to know how to calcuate the days which was spent in each state. Please find the find the sample data below.

 DateFormat Work Item Id Title Assigned To State 3/31/2021 1143 XYZ Raj New 4/30/2021 1143 XYZ Raj New 5/31/2021 1143 XYZ Raj New 6/30/2021 1143 XYZ Raj UAT 7/31/2021 1143 XYZ Raj UAT 8/31/2021 1143 XYZ Raj Closed 3/31/2021 1148 ABCD Raj New 4/30/2021 1148 ABCD Raj New 5/31/2021 1148 ABCD Raj New 6/30/2021 1148 ABCD Raj Build 7/31/2021 1148 ABCD Raj Hypercare 8/31/2021 1148 ABCD Rani Closed

Output:

 Work Item Id Title State Days Spent 1143 XYZ New 91 1143 XYZ UAT 31 1143 XYZ Closed 1 1148 ABCD New 91 1148 ABCD Build 31 1148 ABCD Hypercare 31 1148 ABCD Closed 1

1 ACCEPTED SOLUTION
Community Support

First ,the statu should transform to number so that the machine can identify the order between the state.

Whatever you use DAX or query editor to replace the values, all will be ok.

DAX to replace

Crate a new column

``````replace_the_state =
SWITCH ( [State], "New", 1, "UAT", 2, "Build", 3, "Hypercare", 4, "Closed", 5 )``````

Then create a measure

``````ureMeas =
VAR _start_day =
CALCULATE (
MIN ( [DateFormat] ),
FILTER (
ALL ( 'Table' ),
[Title] = MAX ( [Title] )
&& [replace_the_state] = MAX ( [replace_the_state] )
)
)
VAR _next_state_number =
CALCULATE (
MIN ( [replace_the_state] ),
FILTER (
ALL ( 'Table' ),
[Title] = MAX ( [Title] )
&& [replace_the_state] > MAX ( [replace_the_state] )
)
)
VAR _end_day =
CALCULATE (
MIN ( [DateFormat] ),
FILTER (
ALL ( 'Table' ),
[Title] = MAX ( [Title] )
&& [replace_the_state] = _next_state_number
)
)
RETURN
IF ( MAX ( [replace_the_state] ) = 5, 1, VALUE ( _end_day - _start_day ) )``````

And the result

Here is my pbix file you can reference.

Best Regards

Community Support Team _ chenwu zhu

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

4 REPLIES 4
Super User

Hi,

Could you explain how you got the results in the Days spent column.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

First ,the statu should transform to number so that the machine can identify the order between the state.

Whatever you use DAX or query editor to replace the values, all will be ok.

DAX to replace

Crate a new column

``````replace_the_state =
SWITCH ( [State], "New", 1, "UAT", 2, "Build", 3, "Hypercare", 4, "Closed", 5 )``````

Then create a measure

``````ureMeas =
VAR _start_day =
CALCULATE (
MIN ( [DateFormat] ),
FILTER (
ALL ( 'Table' ),
[Title] = MAX ( [Title] )
&& [replace_the_state] = MAX ( [replace_the_state] )
)
)
VAR _next_state_number =
CALCULATE (
MIN ( [replace_the_state] ),
FILTER (
ALL ( 'Table' ),
[Title] = MAX ( [Title] )
&& [replace_the_state] > MAX ( [replace_the_state] )
)
)
VAR _end_day =
CALCULATE (
MIN ( [DateFormat] ),
FILTER (
ALL ( 'Table' ),
[Title] = MAX ( [Title] )
&& [replace_the_state] = _next_state_number
)
)
RETURN
IF ( MAX ( [replace_the_state] ) = 5, 1, VALUE ( _end_day - _start_day ) )``````

And the result

Here is my pbix file you can reference.

Best Regards

Community Support Team _ chenwu zhu

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

Frequent Visitor

@v-chenwuz-msft  Can you please look at my other question which is similar to this one and tell me what is wrong with my calculation

Super User

@rbangari001 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Announcements