Forum Discussion
Post ProdigyHow 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/2024
ABC Review 1/16/2024
ABC Done 2/1/2024
DEF Backlog 10/20/2024
DEF In Progress 11/1/2024
What I need is a calculated column next to each to check how long it has been in that status for example "ABC has been in Backlog from 1/1/2024-1/15/2024", so 14 days total. using 11/14/2024 as the current date
IssueKey Status FirstTransitionTo DaysInStatus
ABC Backlog 1/1/2024 14
ABC In Progress 1/15/2024 1
ABC Review 1/16/2024 37
ABC Done 2/1/2024 288
DEF Backlog 10/20/2024 13
DEF In Progress 11/1/2024 14 ----> This is the last status, so use current date in calc
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.
3 Replies
- Gabry
Super User
Check this out
DaysInStatus = VAR CurrentDate = 'Tabella'[ FirstTransitionTo] VAR NextDate = CALCULATE( MIN('Tabella'[ FirstTransitionTo]), FILTER( 'Tabella', 'Tabella'[IssueKey ] = EARLIER('Tabella'[IssueKey ]) && 'Tabella'[ FirstTransitionTo] > CurrentDate ) ) RETURN IF( ISBLANK(NextDate), DATEDIFF(CurrentDate, TODAY(), DAY), DATEDIFF(CurrentDate, NextDate, DAY) ) 
EaglesTonyI 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
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
