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

blazko's avatar
blazko
Helper III
7 years ago

hi, can I do that in M?

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.