Forum Discussion
Jebilaya
Helper III
Helper IIILookup Value error
I am trying to get a calculated column to return an alternative drop departure time. I've created a column to give a unique route and drop ID for each row and I need to be able to return the plan...
JebilayaHelper III
Helper III
Not sure how to supply sample data SpartaBI - can't see an attach option but i've put a table below
The DAX i'm trying to use is
PlanDepartPrior =
var dropprior = 'Microlise Files'[DropID]-1
Return
LOOKUPVALUE('Microlise Files'[Planned departure],'Microlise Files'[UniqueDrop],CONCATENATE('Microlise Files'[UniqueRoute],dropprior))
So basically drop 2 should return the departure time of drop 1
| Order Reference | Actual arrival | Actual departure | Planned arrival | Planned departure | UniqueDrop | DropID | Expected Result |
| 1024657582 | 1899-12-30 06:47:00 | 1899-12-30 06:56:00 | 1899-12-30 06:43:00 | 1899-12-30 06:50:00 | 5/15/2022259-101 | 1 | Blank |
| 1025032718 | 1899-12-30 09:34:00 | 1899-12-30 09:46:00 | 1899-12-30 09:47:00 | 1899-12-30 09:58:00 | 5/15/2022259-1010 | 10 | 1899-12-30 09:43:00 |
| 1005202623 | 1899-12-30 09:50:00 | 1899-12-30 10:03:00 | 1899-12-30 10:02:00 | 1899-12-30 10:15:00 | 5/15/2022259-1011 | 11 | 1899-12-30 09:58:00 |
| 1024959827 | 1899-12-30 10:08:00 | 1899-12-30 10:15:00 | 1899-12-30 10:15:00 | 1899-12-30 10:31:00 | 5/15/2022259-1012 | 12 | 1899-12-30 10:15:00 |
| 1025080963 | 1899-12-30 10:22:00 | 1899-12-30 10:27:00 | 1899-12-30 10:37:00 | 1899-12-30 10:42:00 | 5/15/2022259-1013 | 13 | 1899-12-30 10:31:00 |
| 1024861453 | 1899-12-30 10:36:00 | 1899-12-30 10:42:00 | 1899-12-30 10:50:00 | 1899-12-30 10:58:00 | 5/15/2022259-1014 | 14 | 1899-12-30 10:42:00 |
| 1023915193 | 1899-12-30 10:52:00 | 1899-12-30 10:59:00 | 1899-12-30 11:03:00 | 1899-12-30 11:28:00 | 5/15/2022259-1015 | 15 | 1899-12-30 10:58:00 |
| 1024341888 | 1899-12-30 11:06:00 | 1899-12-30 11:10:00 | 1899-12-30 11:35:00 | 1899-12-30 11:42:00 | 5/15/2022259-1016 | 16 | 1899-12-30 11:28:00 |
| 1024904812 | 1899-12-30 11:18:00 | 1899-12-30 11:30:00 | 1899-12-30 11:49:00 | 1899-12-30 12:00:00 | 5/15/2022259-1017 | 17 | 1899-12-30 11:42:00 |
| 1024723657 | 1899-12-30 11:35:00 | 1899-12-30 11:43:00 | 1899-12-30 12:03:00 | 1899-12-30 12:12:00 | 5/15/2022259-1018 | 18 | 1899-12-30 12:00:00 |
| 1023868591 | 1899-12-30 11:59:00 | 1899-12-30 12:05:00 | 1899-12-30 12:15:00 | 1899-12-30 12:23:00 | 5/15/2022259-1019 | 19 | 1899-12-30 12:12:00 |
| 1024346227 | 1899-12-30 07:05:00 | 1899-12-30 07:17:00 | 1899-12-30 07:00:00 | 1899-12-30 07:19:00 | 5/15/2022259-102 | 2 | 1899-12-30 06:50:00 |
| 1025112226 | 1899-12-30 07:40:00 | 1899-12-30 07:49:00 | 1899-12-30 07:21:00 | 1899-12-30 07:56:00 | 5/15/2022259-103 | 3 | 1899-12-30 07:19:00 |
| 1024849229 | 1899-12-30 07:56:00 | 1899-12-30 08:01:00 | 1899-12-30 08:03:00 | 1899-12-30 08:10:00 | 5/15/2022259-104 | 4 | 1899-12-30 07:56:00 |
| 1024431577 | 1899-12-30 08:03:00 | 1899-12-30 08:07:00 | 1899-12-30 08:11:00 | 1899-12-30 08:17:00 | 5/15/2022259-105 | 5 | 1899-12-30 08:10:00 |
| 1025107193 | 1899-12-30 08:11:00 | 1899-12-30 08:13:00 | 1899-12-30 08:20:00 | 1899-12-30 08:27:00 | 5/15/2022259-106 | 6 | 1899-12-30 08:17:00 |
| 1025056921 | 1899-12-30 08:18:00 | 1899-12-30 08:21:00 | 1899-12-30 08:31:00 | 1899-12-30 08:36:00 | 5/15/2022259-107 | 7 | 1899-12-30 08:27:00 |
| 1024813974 | 1899-12-30 08:39:00 | 1899-12-30 08:55:00 | 1899-12-30 08:42:00 | 1899-12-30 08:52:00 | 5/15/2022259-108 | 8 | 1899-12-30 08:36:00 |
| 1025104007 | 1899-12-30 09:18:00 | 1899-12-30 09:28:00 | 1899-12-30 09:33:00 | 1899-12-30 09:43:00 | 5/15/2022259-109 | 9 | 1899-12-30 08:52:00 |