Forum Discussion

tjn_1992's avatar
tjn_1992
Frequent Visitor
4 years ago
Solved

Calculating time spent in sub status

Hi all, I have a table called "Sub Status" which has three columns: ID, Date_Time, and Sub Status.  I would like to create a column in this table which calculates how much time in hours an ID...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi tjn_1992 ,

     

    Please create an index column.

    index = int(RIGHT('Sub Status'[Sub Status],1))

    Then create the a column like below:

    hours = 
var next_time = CALCULATE(MIN('Sub Status'[Date_Time]),FILTER('Sub Status','Sub Status'[ID]=EARLIER('Sub Status'[ID])&&'Sub Status'[Date_Time]>EARLIER('Sub Status'[Date_Time])))
return
DATEDIFF('Sub Status'[Date_Time],next_time,MINUTE)/60

     

    Best Regards,

    Jay

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi tjn_1992 ,

 

Please create an index column.

index = int(RIGHT('Sub Status'[Sub Status],1))

Then create the a column like below:

hours = 
var next_time = CALCULATE(MIN('Sub Status'[Date_Time]),FILTER('Sub Status','Sub Status'[ID]=EARLIER('Sub Status'[ID])&&'Sub Status'[Date_Time]>EARLIER('Sub Status'[Date_Time])))
return
DATEDIFF('Sub Status'[Date_Time],next_time,MINUTE)/60

 

Best Regards,

Jay

  • tjn_1992's avatar
    tjn_1992
    Frequent Visitor
    4 years ago

     This solution worked for me. Thank you for your help, Jay!