Forum Discussion

rjsidek's avatar
rjsidek
Icon for Helper II rankHelper 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
Icon for Community Champion rankCommunity 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
Icon for Helper II rankHelper 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
    Icon for Community Champion rankCommunity 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