Forum Discussion

Scottnap's avatar
Scottnap
Frequent Visitor
2 years ago
Solved

Trying to do DATEDIFF across rows in a table using measure

I have a table that gives me the change history for work items.  The date/time of each change is stored in the ChangeDate field.
I'm trying to calculate how ong it takes an item to transition from when it is changedTo "EXTERNAL RCA IN PROGRESS" to "EXTERNAL RCA UNDER REVIEW"

The dates I need to diff are in the same column, however the dates I need to calcuatle are in different rows.  I have 3 different measures.

First measure - get the In_Progress_Start date

 

In_Progress_Start = CALCULATE(MIN(DIRCA_Transitions[DIRCA.changelog.histories.created]),DIRCA_Transitions[DIRCA.changelog.histories.items.toString]="EXTERNAL RCA IN PROGRESS")

 

 Second Measure get the In_progress_Finish date

 

In_Progress_Finish = CALCULATE(MIN(DIRCA_Transitions[DIRCA.changelog.histories.created]),DIRCA_Transitions[DIRCA.changelog.histories.items.toString]="EXTERNAL RCA UNDER REVIEW")

 

Finally, calculate the difference between the two dates.

 

Time_In_Progress = 
VAR DIRCA_FILTER = VALUES(DIRCA_Transitions[DIRCA.key])
RETURN
SUMX(KEEPFILTERS(DIRCA_FILTER),DATEDIFF(DIRCA_Transitions[In_Progress_Start],DIRCA_Transitions[In_Progress_Finish],DAY))

 

The first two measures work fine, they accurately give me the start and finish times for the phase I want to measure.  However the third measure yields nothing.  Can someone please help me figure out what I'm doing wrong here?  Seems like it should be simple but I've been banging my head on this for hours now!

  • Scottnap , Try like

    SUMX(VALUES(DIRCA_Transitions[DIRCA.key]),DATEDIFF([In_Progress_Start],[In_Progress_Finish],DAY))

2 Replies

  • Scottnap , Try like

    SUMX(VALUES(DIRCA_Transitions[DIRCA.key]),DATEDIFF([In_Progress_Start],[In_Progress_Finish],DAY))

    • Scottnap's avatar
      Scottnap
      Frequent Visitor

      Thank you!  That did the trick!