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

[EaglesTony]

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

[Kedar_Pande]

@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

[EaglesTony · Accepted solution]

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.

[Gabry]

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)
    )