Forum Discussion
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 |
| 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
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
5 Replies
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 )This doesnt seem to have worked. I dont see anything for this measure.
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
Hi,
Drag Identifier to the row labels and use this measure
Date Diff in Days = MAX(Data[Completion_Date])-MIN(Data[Completion_Date])
