Forum Discussion

Kumar11109's avatar
Kumar11109
Helper IV
9 years ago

Time Difference Between two resources

Hi All,    I am facing another problem with my data set. I am attaching an image for a sample of my dataset.    The data set contains the time spent by children in a session on different resource...
Need Help
Kumar11109's avatar
Kumar11109
Helper IV
9 years ago

Phil_SeamarkI don't think so this is what I want. If you look at the column "Time Spent" and the difference between end time for resource 11 and start time for resource 12 is 00:1:53 seconds and not 5 mins and 30 seconds. 
This is the problem, I hope I am a bit more clear now. 
Thank You for your quick reply BTW. 
And it would be really appreciated if we can do this without going into the query, if possible and by just adding a column. 

 

Thank You, 

Kumar Ashwarya 

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Kumar11109

 

Hi Kumar Ashwarya,

 

According to your conditions, the records in this table should have an order. So firstly, we add an index column. Then we can use this formula in calculated column to get the time spent.

TimeSpent =
VAR NextStartTime =
    CALCULATE (
        MIN ( Table1[Start Time] ),
        FILTER (
            Table1,
            Table1[Index]
                >= EARLIER ( Table1[Index] ) + 1
                && Table1[Session ID] = EARLIER ( Table1[Session ID] )
        )
    )
RETURN
IF ( ISBLANK ( NextStartTime ), BLANK (), NextStartTime - Table1[End Time] )

 Ps: 1. Column NextStartTime is just to show the process, you don’t need to add it.

       2. Pay attention to the data type.