Datediff between rows in subgroups of rows

[HxH]

Hi guys, 

I have a table with userID wich identifies the user, date which identifies the day, Action which can be a list of things, time which records the time the action was done .

What i need to do is calculate the datediff in seconds between two actions (so between the current row and the next) and I need to do it gruping by userID and date, so that in the end I can sum this results and obtain the total time "worked" (I define time worked as the time spans between two rows) for each user in each day. I think I should use VARs to identify the rows and Allexcept to create the subgroup but I'm stuck 

Thanks for your help 

[Cmcmahan]

So just so that I understand your problem correctly:

• You want to calculate a duration between two actions
• Each row of data is an action and has a userID, date, action type, and timestamp.
• Rows should be grouped by userID and date.  It doesn't make sense for a duration to go over midnight, or to get a duration of different userIDs.  It also does not matter what the action type is.
• Once grouped, rows should be ordered by timestamp.  This way, the "next" row of data is always the next event in time.  
  • Is it possible for two events by the same person to have the same timestamp? I'm not sure how precise your timestamps are.

As far as the measure used to get this, it depends on how you plan to display it.  If it's in a tabular visual like a matrix, with date and userID as the rows, then the matrix visual is doing your grouping already.  If you're trying to display it in a card, it will need a slightly tweaked formula, since the grouping isn't done automatically.  Let us know how you intend to display/use this measure, and we can help more specifically.

This should get you started, and would be the general form, assuming you have another mechanism to filter down to one User/Date combo (the visual itself, a set of slicers, page filters, etc):

Hours Worked = 
SUMX(
    Table1[timestamp], 
    DATEDIFF( 
        CALCULATE( 
            MAX(Table1[timestamp]), 
            FILTER(ALLEXCEPT(Table1, Table1[userID], Table1[date]), Table1[timestamp]<SELECTEDVALUE(Table1[timestamp]))
        ),
        Table1[timestamp],
        SECOND
    )
)