DATEDIFF with multiple criteria

Question

OK So Im learning Power Bi.So I have a table that has multiple Id's (LID) and it logs when that particular LID reashes a status in our system.So I made a Max(Date) in the main table and it works good (max due to the LID can be switched back to a previous status multiple times so I need the most recent date)What I am trying to accomplish is to get the day count between cetrin status.So you can see that this LID was in 5 different statuses. So just so I can get the basic formula down can someone help me get the following:DATEDIFF for the LID = 'zzrRz9d5Rvrn' from 'New Lead' (11/26/21) to 'Awaiting Documents' (12/10/21)&nbsp;The result in Table2 under 'New to AwaitDocs' should be 14 (indicating 14 days between&nbsp;11/26/21 and&nbsp;12/10/21)&nbsp;I have to do this between multiple different statuses but just asking for a general formula for the above, after that I should be able to figure it out from there.Please and Thank you!&nbsp;

tackytechtom · Answer

Hi&nbsp;Eric_Tribble&nbsp;,&nbsp;First add an index to your table in Power Query:&nbsp;After that you can create a calculated column with this code:TomsNewColumn = 
DATEDIFF (
    CALCULATE (
        MAX ( 'Table'[LDate] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    ),
    'Table'[LDate],
    DAY
)&nbsp;You should end up with something like this depending on the index:&nbsp;&nbsp;Did this do the trick for you? 🙂&nbsp;/Tomhttps://www.tackytech.bloghttps://www.instagram.com/tackytechtom&nbsp;