Forum Discussion

blazko's avatar
blazko
Helper III
8 years ago
Solved

calculating previous time value

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!

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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.