Getting the date of following ID to calculate (datediff) between initial date and final date

[emf_efab]

I need to get the date of the next ID in the same Order (Pedido), to make afterwards a DateDiff and have as a result the days spended in that state (Estado). For example I need to fill up the blue column (Dia_Sig) with the "correct" date. In ID "31" I should have data as shown, plus "Dia_Sig" 20/02/2021, to then calculate the days and have a next column with "18" as a result.

I need to know how many days I spend in each "Estado", having as final result that the Order (Pedido) lasted 38 days, between 01/02/2021 (Initial Date) and the (Last Date) 11/03/2021. I have the PBIX, and Excel file to attach and send, for better understanding.

[emf_efab]

The pbix Order Ctrl and the Excel Testing 

[Mohammad_Refaei · Accepted solution]

Since you want to create a calculated column then you can write it as follows:

Dia_Sig = 
Var SelectedID = 'Control Emails'[ID]
RETURN
CALCULATE (
    Min ( 'Control Emails'[Fecha] ),
    FILTER (
        ALLEXCEPT ( 'Control Emails', 'Control Emails'[Pedido#] ),
        'Control Emails'[ID] > SelectedID
    )
)

The measure to calculate he duration could be:

Duration = 
IF (
    HASONEVALUE ( 'Control Emails'[Pedido#] ),
    DATEDIFF (
        MIN ( 'Control Emails'[Fecha] ),
        MAX ( 'Control Emails'[Dia_Sig] ),
        DAY
    )
)

(last column)

Please check the adjusted file.

[emf_efab]

Thank you very much, I can now go forward, with this help. Regards!!!

[emf_efab]

 I need to calculate the Fecha_Modif as it is shown in the picture...that should be Dia_Sig, not the one I have now in blue. That should be the next (Initial) Date, for the next ID of the same Order (Pedido#). I shouldn't have Blanks!! >> Id 11 should be 01/02/2021, and Id 31 20/02/2021  

[emf_efab]

It doesn't work or I don't understand what you want to do...Which should be the way to attach the report, and excel file. Sorry I am new in the Forum, and don't know to work with it.

[Mohammad_Refaei]

You may upload the file to your OneDrive or GoogleDrive and share the link here

[Mohammad_Refaei]

You can try this calculated column:

Dia_Sig =
CALCULATE (
    MAX ( [Fecha] ),
    FILTER (
        ALL ( MyTable ),
        MyTable[Pedido] = MyTable[Pedido]
            && MyTable[Fecha] > MyTable[Fecha]
    )
)