Number of Days from earliest date to latest date based on changes in 2 columns

[ABR002]

I am running into another issue with this project, I need to calculate the number of days an employee spends in a status before changing statuses:

Name	Status	Period End Date
Dave	Active	6/30/2022
Shelly	Inactive	6/30/2022
Roy	Inactive	6/30/2022
Tom	Active	6/30/2022
Monica	Active	6/30/2022
Dave	Inactive	6/23/2022
Shelly	Inactive	6/23/2022
Roy	Inactive	6/23/2022
Tom	Inactive	6/23/2022
Monica	Active	6/23/2022
Dave	Inactive	6/16/2022
Shelly	Inactive	6/16/2022
Roy	Inactive	6/16/2022
Tom	Active	6/16/2022
Monica	Active	6/16/2022

I would just be going of the number of days from period end date to period end data. I'd need to basically say that from Above, Dave was in Inactive Status for 14 Days, as he first had an inactive status on 6/16 and that changed 14 days later on 6/30. Thanks as always

[SQL_SousChef · Accepted solution]

Maybe try this?

DaysUntilNextDate =

VAR CurrentRowDate = [Period End Date]

VAR CurrentName = [Name]

VAR NextDateIndex =

    CALCULATE(

        MAX('TeamData'[Period End Date]),

        FILTER(

            'TeamData',

            'TeamData'[Name] = CurrentName && 'TeamData'[Period End Date] > CurrentRowDate

        )

    )

RETURN

    IF(

        ISBLANK(NextDateIndex),

        BLANK(),

        NextDateIndex - CurrentRowDate

    )