Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
89 | |
87 | |
77 | |
69 | |
68 |
User | Count |
---|---|
222 | |
128 | |
117 | |
82 | |
77 |