DATEDIFF with multiple criteria

[Eric_Tribble]

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)

The result in Table2 under 'New to AwaitDocs' should be 14 (indicating 14 days between 11/26/21 and 12/10/21)

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!

[tackytechtom]

Hi @Eric_Tribble ,

First add an index to your table in Power Query:

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
)

You should end up with something like this depending on the index:

Did this do the trick for you? 🙂

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[Eric_Tribble]

Is there a way to make the Index pull from a list so I can assign the status's in order?
1= New,

2=awaiting docs,

3=STR Review Call,

etc

[Eric_Tribble]

My end result would probaly me a matrix type table

LID List |  Status 1  |   Status 2    |   Status 3   | 

__________________________________________________________

LID1     | startDate | 2-1 = Days | 3-2 = Days | 

somthing like that