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
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.

 

 

Kumar11109's avatar
Kumar11109
Helper IV
9 years ago

I tried using your method, but it is showing errors. 

The error is "Token Eof expected". 

Do I need to create this column out of the query or in the query editor only? 


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

    Kumar11109

     

    Hi,

     

    We add index in the Query Editor. And then add a calculated column in the table with the formula. These two steps are performed in different place. The error showed whether you were in the wrong places or you missed some parenthesis (usually right parenthesis). Please check it out.

     

    Best Regards!

    Dale