Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help with DATEDIFF

That was my problem.

 

I have a table with projects where one of the columns indicates in which phase this project is.

I also have columns indicating each phase of the project. Where I put the date when the project goes into that phase.

 

I would like to create a measure using DATEDIFF(Date1;Today();Day) to see how long does each project are in the phase.

 

But how do I make Date1 be the column corresponding to the phase the project is in?

 

ProjectStageIdeaBusiness CaseDevelopmentLaunch
P1Launch01/02/201901/03/201901/04/201901/05/2019
P2Idea01/02/2019   
P3Business Case01/02/201901/03/2019  
P4Development01/02/201901/03/201901/04/2019 
P5Launch01/02/201901/03/2019 01/05/2019
P6Launch01/02/2019 01/04/201901/05/2019
P7Development01/02/2019 01/04/2019 
P8Launch01/02/2019  01/05/2019

 

Example: Today is 05/06/2019

 

If I select P2; I want to see that P2 is on the Idea phase for 124 days.

 

if I select P3; I want to see that P3 is on the Business Case phase for 96 days.

 

Any Ideas?

 

Need Help
Show and Tell
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous 

    Just try this formula to create a column:

    NewDaysInStage = 
IF (
    Table1[LatestStage] = Table1[Stage],
    DATEDIFF ( Table1[StageDate], TODAY () , DAY ),
    DATEDIFF (
        Table1[StageDate],
        CALCULATE (
            MIN ( Table1[StageDate] ),
            FILTER (
                Table1,
                Table1[Project] = EARLIER ( Table1[Project] )
                    && Table1[StageDate] > EARLIER ( Table1[StageDate] )
            )
        ),
        DAY
    )
)

    Best Regards,

    Lin

     

7 Replies