Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
hi, can I do that in M?
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |