Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
phyten
Helper I
Helper I

Calculate time between to states with different rows

timestatus3.jpg

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

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.

View solution in original post

4 REPLIES 4
Baskar
Resident Rockstar
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 

austinsense
Impactful Individual
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 🙂

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
Resolver II
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

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.