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.

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

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