Calculate Phase/Stage duration

Hi Anonymous ,

Is it this you are looking for?

I solved it in two steps with two different calculated columns.

First, we need to create an index column so we can iterate through each row and calculate the difference between their respective dates. Since we have rows which have the same date, we do need to have a second column deciding which one to rank first or second. In your case, I chose to_stage_id, so the ranking looks like this:

index = 
VAR MaxToStageID = MAX( 'Table'[to_stage_id] )
VAR result =
    RANKX (
        ALL ( 'Table' ),
        'Table'[activity_created] * MaxToStageID + 'Table'[to_stage_id],
        ,
        ASC,
        DENSE
    )
RETURN
    result

The guys from sqlbi have done a blog about this topic on how to rank / create indexes on multiple columns.

After that, I created another calculated column which uses the index to iterate through the table calculating the date difference:

tomstest = 
VAR Index =  ('Table'[index])
VAR PreviousIndex = ('Table'[index] - 1 )
VAR result = 
        DATEDIFF( 
            CALCULATE ( 
                VALUES ( 'Table'[activity_created] ),
                FILTER ( ALL ('Table'), 'Table'[index] = PreviousIndex )  
            ),
            CALCULATE ( 
                VALUES ( 'Table'[activity_created] ),
                FILTER ( ALL ('Table'), 'Table'[index] = Index )  
            ),
            DAY
        )
RETURN result

Hope this helps!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom