Calculate Phase/Stage duration

[Anonymous]

Hi Dear Power Bi Helpers,

I need some advice. I would like to calculate the Phase duration for all candidates in my data .

Is there any proper way how to solve it?

This is how my data looks (please see my photo attached).

Explanation:

Inbox - Reviewing (11 May-14 May))-4 days

Reviewing -1st Interview (14 May) - 1 day

1st Interview -Selection1 (14 May-19 May) - 6 days

...and so on

till the final hiring stage 14 june for this person.

Any help much appreciated.

Thank you a lot!

[v-luwang-msft]

Hi @Anonymous  @Anonymous ,

You could also  create index through Power Query Editor:

And you could download pbix file to check with your file.

Best Regards

Lucien

[Anonymous]

Hi Tom, Thank you for the effort and your solution .

Yes, what you have achieved will work for me as a final goal, but when I replicate your solution in my data I get 0 as a result. 

I think the problem lies in the column  to_stage_id. The Id's are maybe not a good idea for sorting.

I have also tried to create the Index column with this logic, but its not giving me the desired result.

Index =

RANKX('Job Application FT', 'Job Application FT'[activity_created].[Date], , ASC, Dense)

Any other idea what could be the problem?

[Anonymous]

I have tried this formula in a calculated column, it is near what i what but not exactly.

Test Column =

VAR temp =

TOPN (

1,

FILTER (

'table',

'table''[candidate_id] = EARLIER ( 'table''[candidate_id])

&& 'table''[activity_created] < EARLIER ( 'table''[activity_created])

),

'table''[activity_created], DESC

)

RETURN

DATEDIFF ( MINX ( temp, 'table''[activity_created] ), 'table''[activity_created], DAY )

This is the result I got

Reviewing -1st Interview (14 May) - 1 day not 3.

and the total of  48 is not not correct. there are 35 days (11 may-14 june).

Can you also show me how can I calculate the correct total days from start date till the end date?

[tackytechtom · Accepted solution]

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

[Cortana]

Hello there!

I was looking for something similar. But found this. 

My question is my interest is to calculate how long the opportunities are staying in each stage. So in your case, when the opportunity entered stage 2, the date was the 14th of May, and when it entered stage 3 the date was the 19th of May. So the duration for stage 2 for this opportunity should be 5. But in your case, it is showing 0 days. That is the result of the previous stage. 

Did I get it wrong?

[tackytechtom]

Hi @Cortana ,

Have a look in here.

There, we cover two different cases on calculating the date difference between rows.. Otherwise, you can just open a new topic, add some sample data and tag me.

I am more than happy to have a look into it! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Cortana]

Thank you so much for that.  I have already asked a question about that and it's solved. I was asking for this