Getting Date Difference between Stages in the same column

[rjsidek]

Hi everyone,

I currently have a sample dataset that looks like this:

What I am trying to achieve is this:

I am looking to create a measure that tells me how long it takes for the "Project Stage" column to move from one stage to another. I believe that I have to use the DATEDIFF function somehow but I am not yet able to do it. 

For example, I want to be able to see the time it takes for the company to move from stage 1 to stage 2.

Any help would be appreciated and thanks in advance!

[amitchandak]

One of the way is use SUMMARIZE and group data at the project level and then take a date diff

SUMMARIZE(Table,Table[Peoject],"Stage1",Minx(filter(table, stage ="sage1"),table[Date]),"stage2",Minx(filter(table, stage ="sage1"),table[Date]))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[az38]

Hi @rjsidek 

try a measure like

Measure = 
var InitialStage = calculate(min('Table1'[Date]);ALLEXCEPT('Table1';'Table1'[Company]);'Table1'[Stage]="1 - HOD Approval")
var SecondStage = 
calculate(min('Table1'[Date]);ALLEXCEPT('Table1';'Table1'[Company]);'Table1'[Stage]="2-IC Review")
RETURN
DATEDIFF(InitialStage; SecondStage; DAY)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[rjsidek]

Hi @az38 ,

Thanks for your reply. I tried it, and it works to an extent.

However, as you can see from the dataset, there are multiple stages and not just 2. Is there a way I can expand this measure to include any and all the 8 stages? 

Best, 

[az38]

@rjsidek 

whats your desired output?

do you want to see period between stages in different columns?

or you want to see it in 1? or do you want to aggregate it?

Now, its unclear for me, sorry

do not hesitate to give a kudo to useful posts and mark solutions as solution

[rjsidek]

Thanks for clarifying @az38 ,

@amitchandak 

If it helps, here is how my pbi template looks like

I have created measure with the code you ave, and it is given me the difference between Stage 2 and Stage 1.

What I would ultimately like to achieve is this:

Notice that I put in a slicer for project stage in the template. The idea is, when 1 click stage 2 on the slicer, power bi takes the difference between stage selected and previous stage, in this case would be datediff of stage 2 and stage 1. If i choose stage 3, I hope it will take the diff between stage 3 and stage 2 etc. Is this possible?

Thanks!

[az38 · Accepted solution]

@rjsidek 

how it should look if no one slicer is chosen?

anyway, try a measure

Measure = 
var SecondStage = 
calculate(min('Table1'[Date]);ALLEXCEPT('Table1';'Table1'[Company];Table1[Stage]))
var Init = calculate(max('Table1'[Stage]);ALLEXCEPT('Table1';'Table1'[Company]);'Table1'[Date]<SecondStage;'Table1'[Stage]<>"")
var InitialStage = if(isblank(Init);SecondStage;calculate(min('Table1'[Date]);FILTER(ALL('Table1');'Table1'[Company]=SELECTEDVALUE(Table1[Company])&&'Table1'[Stage]=Init)))
RETURN
DATEDIFF(InitialStage; SecondStage; DAY)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[rjsidek]

@az38 it worked!

Currently, it when no slicer is chosen, it is just displaying 0. Is there a way to let it show a text string instead?

[az38]

@rjsidek 

try ISFILTERED function

Measure = 
var SecondStage = 
calculate(min('Table1'[Date]);ALLEXCEPT('Table1';'Table1'[Company];Table1[Stage]))
var Init = calculate(max('Table1'[Stage]);ALLEXCEPT('Table1';'Table1'[Company]);'Table1'[Date]<SecondStage;'Table1'[Stage]<>"")
var InitialStage = if(isblank(Init);SecondStage;calculate(min('Table1'[Date]);FILTER(ALL('Table1');'Table1'[Company]=SELECTEDVALUE(Table1[Company])&&'Table1'[Stage]=Init)))
RETURN
IF(OR(ISFILTERED(Table1[Company]);ISFILTERED(Table1[Stage]));DATEDIFF(InitialStage; SecondStage; DAY);"No filters")

 do not hesitate to give a kudo to useful posts and mark solutions as solution