calculating previous time value

[blazko]

Hi.

I'm having a problem calulating a column with previous record time.

My table is : date, user, time. I need to put nwe column with like one step backward action.

So when a cartain user has activity in time column : 13:10, 13:20, 13:50, i want to have in one row 13:20 and (time), and 13:10 (previous time).

Thanks for the response in advance!

[Anonymous · Accepted solution]

This should do it:

Previous Time =
VAR CurrentTime = Table1[Time]
VAR CurrentUser = Table1[User]
VAR CurrentDate = Table1[Date]
RETURN
    CALCULATE (
        MAX ( Table1[Time] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Time] < CurrentTime
                && Table1[User] = CurrentUser
                && Table1[Date] = CurrentDate
        )
    )

Note that the column will show a blank value if it is the first time for a given date.

[blazko]

hi, can I do that in M?

[Anonymous]

@blazko , you could accomplish this by doing a self join.

In Query Editor, merge the table to itself...join on User and Date, left outer join.  Then expand the nested table, and aggregate the time column to MIN.

Let me know if you need the exact M code, but this should be feasible solely through the UI.

[blazko]

Thanks 🙂