Calculate time between to states with different rows

[phyten]

I want to calculate the time difference between two states.

Not sure if it is import to mention:
Same state changes can occur more than once: f.e. key: A  "in development" -> "ready" happens twice. The calculation should be able to differentiate. Later on I will add those times in order to get a total time in state per key.

[Baskar]

1. First u have to create index for this in Query Editor window using Power Query 

2. create Calculated Column 

Column = 

var get_cur_Index = Index Field Name

var get_cur_key    = Key  Field Name

var get_cur_Date   = Date Filed Name 

Var get_min_Index = calculate (  min(Index) ,

                                                     filter( all(TableName), Key = get_cur_key )

                                                  )

var Min_Check = if (  get_cur_Index  = get_min_Index , 0 , 1 )

var get_Previous_Date = if ( Min_Check =0 , Blank() , calculate ( min(Date Filed Name) , filter (TableName, Key = get_cur_key -1 ) ) )

Return   if ( Min_Check =0 , Blank(), datediff ( get_Previous_Date , get_cur_Date   )  )

This will solve your problem  if not let me know i will help u.

U have to replace the Bold with ur coresponded to your table

Note :

I wont suggest Earlier   Function that create a performance issue 

[austinsense]

Your 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 🙂

[phyten · Accepted solution]

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.

[Framet]

Hi,

If we assume as it looks like is the case that each key can only be in one state at any one time then it is relatively straightforward to calcuate the time in that state, though I note your example returns the time in days that the old status for that row was in effect for.

I have called your table FactTable in the below example:

Difference =
DATEDIFF (
    CALCULATE (
        MAX ( [Date] ),
        FILTER (
            FactTable,
            [Date] < EARLIER ( FactTable[Date] )
                && FactTable[key] = EARLIER ( FactTable[key] )
        )
    ),
    [Date],
    DAY
)

This formula should work as a calculated column and return the values you have highlighted below. It only does so as the calculated column creates a new row context automatically therefore earlier is a valid function. You may need to slightly change the forumla if a "key" can change state within the same day.

Note you can then create a measure to sum this column and order by oldStatus to see how long each items spent on each status.

Let me know if it helps.

Thanks

Thomas