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

[rbangari001]

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

Please help on this.

[Ashish_Mathur]

Hi,

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

[v-chenwuz-msft · Accepted solution]

Hi @rbangari001 

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.

[rbangari001]

@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

[Greg_Deckler]

@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