Forum Discussion

rjsidek's avatar
rjsidek
Helper II
6 years ago
Solved

Getting Date Difference between Stages in the same column

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 "P...
Need Help
  • az38's avatar
    az38
    6 years ago

    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

az38's avatar
az38
Community Champion
6 years ago

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's avatar
rjsidek
Helper II
6 years ago

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's avatar
    az38
    Community Champion
    6 years ago

    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's avatar
      rjsidek
      Helper II
      6 years ago

      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's avatar
        az38
        Community Champion
        6 years ago

        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