cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculate time between to states with different rows

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.

1 ACCEPTED SOLUTION
Helper I

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.

4 REPLIES 4
Resident Rockstar

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

Impactful Individual

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 🙂

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
Helper I

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.

Resolver II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.