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

NidhiBhusari's avatar
NidhiBhusari
Icon for Helper IV rankHelper IV
4 years ago

Hi tjn_1992 ,

Do you have any other Date/Time column in your data?

 

tjn_1992's avatar
tjn_1992
Frequent Visitor
4 years ago

Hi NidhiBhusari ,

In the Sub Status table? No.
I do, however, have a separate Date table which I link to Sub Status.