Forum Discussion
Helper ICalculate time between to states with different rows
Thank you for alle the suggestions and answers.
I only tested one of the solutions until now since I have limited time. I will look further into it after the 9th of january. (yey holidays)
I had to edit austinsense answer a bit but it seems to work now.
diff = VAR vKEY = timetest[key] VAR vINDEX = timetest[Index] RETURN DATEDIFF( CALCULATE( VALUES(timetest[Date]), FILTER( ALL(timetest), timetest[key] = vKEY && timetest[Index] = (vINDEX - 1) ) ),timetest[Date],DAY)Things I changed. Had to add timediff in order to calculated the timeperiod else it would have substracted two dates resulting in another date. I had to switch the minuend and the subtrahend.
Impactful IndividualYour life will be much easier if you can label the rows with an ordered index - {1, 2, 3 ...} - for each project - {A, B, C ...}. Then you could write a calculated column like this ...
= Table[Date} -
CALCULATE( VALUES(Table[Date]),
FILTER( ALL(Table),
Table[Key] = EARLIER(Table[Key] &&
Table[Index] = (EARLIER(Table[Index]) - 1)
)
)You can also write it this way with variables (if you're using Power BI or Excel 2016
=VAR vKEY = Table[Key] VAR vINDEX = Table[Index]
RETURN Table[Date} - CALCULATE( VALUES(Table[Date]), FILTER( ALL(Table), Table[Key] = vKEY && Table[Index] = (vINDEX - 1) ) )
You may also want to include a little error checking
=VAR vKEY = Table[Key]
VAR vINDEX = Table[Index]
RETURN
IF( vINDEX = 1, 0,
Table[Date} -
CALCULATE( VALUES(Table[Date]),
FILTER( ALL(Table),
Table[Key] = vKEY &&
Table[Index] = (vINDEX - 1)
)
))Hope this helps :)
Helper IThank you for alle the suggestions and answers.
I only tested one of the solutions until now since I have limited time. I will look further into it after the 9th of january. (yey holidays)
I had to edit austinsense answer a bit but it seems to work now.
diff = VAR vKEY = timetest[key]
VAR vINDEX = timetest[Index]
RETURN
DATEDIFF(
CALCULATE( VALUES(timetest[Date]),
FILTER( ALL(timetest),
timetest[key] = vKEY &&
timetest[Index] = (vINDEX - 1)
)
),timetest[Date],DAY)Things I changed. Had to add timediff in order to calculated the timeperiod else it would have substracted two dates resulting in another date. I had to switch the minuend and the subtrahend.