Forum Discussion
DATEDIFF with multiple criteria
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 wo...
tackytechtom
Most Valuable Professional
Most Valuable ProfessionalHi 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.instagram.com/tackytechtom
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
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