datediff calculating milestone completion cycle time

[nmeliasp]

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
A	Y	1/23/2018
B	X	1/8/2018
B	Y	1/15/2018
C	X	1/10/2019
C	Y	1/20/2019

My desired output is below

Identifer	Date Diff in Days
A	22
B	7
C	10

I need to figure out how to write a DAX expression to give me my desired output table. Do i need to calculate a new table. I am thinking of needing to use DATEDIFF and GROUPBY but not sure if that is the right approach and looking for suggestions

[Ashish_Mathur]

Hi,

Drag Identifier to the row labels and use this measure

Date Diff in Days = MAX(Data[Completion_Date])-MIN(Data[Completion_Date])

[Zubair_Muhammad]

@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]

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

[v-cherch-msft · Accepted solution]

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]

Awesome that worked!!!