Forum Discussion

nmeliasp's avatar
nmeliasp
Regular Visitor
7 years ago
Solved

datediff calculating milestone completion cycle time

Hello i have the following data and trying to calculate date difference in days between 2 different milestone completions   Identifer Milestone Completion Completion date A X 1/1/2018 ...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi nmeliasp

     

    You may use ALLEXCEPT Function as below:

    Measure = 
VAR MAX_Date =
    CALCULATE (
        MAX ( Table1[Completion date] ),
        ALLEXCEPT(Table1,Table1[Identifer])
    )
VAR MIN_Date =
    CALCULATE (
        MIN( Table1[Completion date] ),
        ALLEXCEPT(Table1,Table1[Identifer])
    )
RETURN
    DATEDIFF ( MIN_Date,MAX_Date, DAY )

     Regards,

    Cherie

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi nmeliasp

 

You may use ALLEXCEPT Function as below:

Measure = 
VAR MAX_Date =
    CALCULATE (
        MAX ( Table1[Completion date] ),
        ALLEXCEPT(Table1,Table1[Identifer])
    )
VAR MIN_Date =
    CALCULATE (
        MIN( Table1[Completion date] ),
        ALLEXCEPT(Table1,Table1[Identifer])
    )
RETURN
    DATEDIFF ( MIN_Date,MAX_Date, DAY )

 Regards,

Cherie

nmeliasp's avatar
nmeliasp
Regular Visitor
7 years ago

Awesome that worked!!!