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

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

  • rjsidek's avatar
    rjsidek
    Helper II
    6 years ago

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

      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