Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I need to solve a selecting issue, didn't find something for weeks now.
I have a list of items (not unique) with entries for several dates (Timestamp).
Each item has a separate due date.
I want to find the last row and then the unique value for "Team" just before the due date has arrived.
Item Number | Deadline | Timestamp | Team |
10612489 | 28.02.2019 09:11:00 | 27.02.2019 10:44:34 | Team 1 |
10612489 | 28.02.2019 09:11:00 | 27.02.2019 10:46:30 | Team 1 |
10612489 | 28.02.2019 09:11:00 | 28.02.2019 08:48:58 | Team 2 |
10612489 | 28.02.2019 09:11:00 | 28.02.2019 12:45:30 | Team 1 |
10612489 | 28.02.2019 09:11:00 | 28.02.2019 22:36:25 | Team 1 |
10612489 | 28.02.2019 09:11:00 | 01.03.2019 08:27:24 | Team 1 |
10612489 | 28.02.2019 09:11:00 | 01.03.2019 19:17:47 | Team 2 |
10616947 | 11.03.2019 11:48:00 | 08.03.2019 09:38:57 | Team 3 |
10616947 | 11.03.2019 11:48:00 | 08.03.2019 09:52:07 | Team 4 |
10616947 | 11.03.2019 11:48:00 | 08.03.2019 09:52:32 | Team 1 |
10616947 | 11.03.2019 11:48:00 | 08.03.2019 17:41:37 | Team 1 |
10616947 | 11.03.2019 11:48:00 | 11.03.2019 22:24:09 | Team 1 |
10616947 | 11.03.2019 11:48:00 | 12.03.2019 08:54:38 | Team 3 |
10617049 | 08.03.2019 15:49:00 | 08.03.2019 11:49:54 | Team 1 |
10617049 | 08.03.2019 15:49:00 | 08.03.2019 13:25:26 | Team 1 |
10617049 | 08.03.2019 15:49:00 | 08.03.2019 13:26:06 | Team 5 |
10617049 | 08.03.2019 15:49:00 | 11.03.2019 09:43:11 | Team 5 |
Solution would be then
Item Number | Team |
10612489 | Team 2 |
10616947 | Team 1 |
10617049 | Team 5 |
I did not manage to summarize that or to use calculate, maybe I am not experienced enough.
Any help is greatly appreciated!
Many thanks in advance!
Jack
Solved! Go to Solution.
Hi @Jack42
I did it in 3 stages.
1. Find if timestamp before deadline - Is Before Dead = IF(Table1[Timestamp] < Table1[Deadline], 1,0)
2. Find the max date -
Hi @Jack42
I did it in 3 stages.
1. Find if timestamp before deadline - Is Before Dead = IF(Table1[Timestamp] < Table1[Deadline], 1,0)
2. Find the max date -
Dear AClerk,
brilliant, works very good. I learned a lot by that... many thanks!
I only needed to replace the kommata in the two „if“ statements with semicoli.
just in case s.o. finds this post and reuses it...
Cheers, Jack 👍🏻
If you want to create this as a calculated column you could use an expression like the following
Last Team = VAR _currentItem = Table1[Item Number] VAR _lastTimeBeforeDeadline = MAXX(FILTER(table1, Table1[Item Number] = _currentItem && Table1[Timestamp] < Table1[Deadline]), Table1[Timestamp]) VAR _lastTeamBeforeDeadline = LOOKUPVALUE(Table1[Team], Table1[Item Number] , _currentItem, Table1[Timestamp], _lastTimeBeforeDeadline) return _lastTeamBeforeDeadline
Dear d_gosbell,
many thanks for your input... unfortunately I could not bring that statement to life - I do something wrong here... Anyhow, the other answer I got was successful, so you do not need to spend more effort in here.
Please again, many thanks for your effort and time! Have a nice day!
Jack 🖖
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |