cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rbangari001
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.

 

DateFormatWork Item IdTitleAssigned ToState
3/31/20211143XYZRajNew
4/30/20211143XYZRajNew
5/31/20211143XYZRajNew
6/30/20211143XYZRajUAT
7/31/20211143XYZRajUAT
8/31/20211143XYZRajClosed
3/31/20211148ABCDRajNew
4/30/20211148ABCDRajNew
5/31/20211148ABCDRajNew
6/30/20211148ABCDRajBuild
7/31/20211148ABCDRajHypercare
8/31/20211148ABCDRaniClosed

 

 

Output:

 

Work Item IdTitleStateDays Spent
1143XYZNew91
1143XYZUAT31
1143XYZClosed1
1148ABCDNew91
1148ABCDBuild31
1148ABCDHypercare31
1148ABCDClosed1

 

 

Please help on this.

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

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

vchenwuzmsft_0-1632705746406.png

 

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.

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-chenwuz-msft
Community Support
Community Support

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

vchenwuzmsft_0-1632705746406.png

 

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.

@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
Super User
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


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors