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

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

    • 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