Need to group/calculate data together based on a column value

[Rads2929]

Hello, I have the following data. The data represents how hot a particular issue is.  I need to calculate days open (Red or Yellow to Close) but the caveat is that when something hits monitor, it stops the clock so I need to split this data out so that I can calculate the time it took to go from Red or yellow to Monitor and then again from Yellow to Closed as represented with ID 1.

ID 1 - measure 1/1/2020 (red start) - 3/1/2020 (monitor start)

ID 1 - measure 6/1/2020 (yellow start) - 6/23/2020 (Closed start)

ID 2 - measure 5/12/2020 (yellow start) - 7/02/2020 (Closed start)

ID 3 - no need to measure because it was never at Red or Yellow.

ID   Level   StartDate  EndDate        

1     Red     1/1/2020  1/7/2020     

1     Yellow 1/7/2020  3/1/2020        

1     Monitor 3/1/2020  6/1/2020     

1     Yellow 6/1/2020  6/23/2020     

1     Closed 6/23/2020 6/23/2020   

2     Yellow  05/12/2020 06/12/2020

2     Yellow  06/12/2020 06/22/2020

2     Yellow  06/22/2020 07/02/2020

2     Closed  07/02/2020 07/02/2020 

3     Monitor 07/01/2020 07/21/2020

3     Closed      07/01/2020 07/21/2020

I thought that maybe I create a calcualted column that would create a subID which would group the necessary rows together but not sure if that is the best way.

[v-chenwuz-msft · Accepted solution]

Hi @Rads2929 

Try this measure:

red and yellow =
VAR _total =
    VALUE( MAX( [EndDate] ) - MIN( [StartDate] ) )
VAR _m_table =
    SUMMARIZE(
        FILTER( 'Table', [Level] = "Monitor" ),
        [StartDate],
        [EndDate],
        "continue_date", [EndDate] - [StartDate]
    )
VAR _monitor =
    SUMX( _m_table, [continue_date] )
RETURN
    _total - _monitor

 Result

Remember, don't add other fields to the table visual, you may get an error if you do so.

I put my pbix file in the attachment 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.