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

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

nmeliasp

 

Try this MEASURE..Drag Identifier and this MEASURE in a Table Visual

 

Measure =
VAR MilestoneY =
    CALCULATE (
        MAX ( Table1[Completion date] ),
        Table1[Milestone Completion] = "Y"
    )
VAR MilestoneX =
    CALCULATE (
        MAX ( Table1[Completion date] ),
        Table1[Milestone Completion] = "X"
    )
RETURN
    DATEDIFF ( MilestoneX, MilestoneY, DAY )

 

 

nmeliasp's avatar
nmeliasp
Regular Visitor
7 years ago

This doesnt seem to have worked. I dont see anything for this measure. 

  • 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!!!