Forum Discussion

EaglesTony's avatar
EaglesTony
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

How can I get a datediff based off the next logical row ?

I have the following table:   IssueKey      Status                        FirstTransitionTo ABC             Backlog                     1/1/2024 ABC             In Progress                1/15/20...
Need Help
  • EaglesTony's avatar
    EaglesTony
    1 year ago

    I could try this.

     

    What I ended up doing was replicating the original table to other tables based off  Status and then did a group by the IssueKey and seems to work.

Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
1 year ago

EaglesTony 

DaysInStatus =
VAR CurrentTransitionDate = 'Table'[FirstTransitionTo]
VAR NextTransitionDate =
CALCULATE(
MIN('Table'[FirstTransitionTo]),
FILTER(
'Table',
'Table'[IssueKey] = EARLIER('Table'[IssueKey]) &&
'Table'[FirstTransitionTo] > CurrentTransitionDate
)
)
VAR EndDate =
IF(ISBLANK(NextTransitionDate), DATE(2024, 11, 14), NextTransitionDate)
RETURN
DATEDIFF(CurrentTransitionDate, EndDate, DAY)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn