Forum Discussion
Dynamically Retrieve Last Observation per ID
Ashish_Mathur wrote:Hi,
I believe this is the result you want. You may download the file from here.
Hope this helps.
Thank you! This is what I was looking for, though I have one final wrinkle:
Suppose that there are two rows with the same ID and matching Date, but have different Type. This creates an error when calculating [Type at datevalue]. I'd like to alter the formulas for [Type at datevalue], [Others at datevalue] and [Vars at datevalue] so that they return only a single value for each ID, preferably the value with the highest row number for a given combination of ID and Date.
I've reposted the data below, adding two lines for ID 32 that create the above conflict.
| ID | Type | Date | Other | Vars |
| 23 | CREATED | 10/23/17 7:39 AM | Fun | Data |
| 23 | UPDATED | 12/28/17 9:23 PM | Fun | Data |
| 23 | DELIVERED | 5/18/18 4:27 PM | Fun | Data |
| 32 | CREATED | 10/31/17 10:56 AM | Fun | Data |
| 32 | CANCELED | 11/15/17 1:27 PM | Fun | Data |
| 32 | CREATED | 12/31/17 9:10 PM | Fun | Data |
| 32 | UPDATED | 12/31/17 9:10 PM | Fun | Data |
| 32 | DELIVERED | 6/16/18 4:26 PM | Fun | Data |
| 71 | CREATED | 5/22/18 4:14 PM | Fun | Data |
| 71 | CANCELED | 5/22/18 5:19 PM | Fun | Data |
| 217 | CREATED | 11/29/17 7:41 AM | Fun | Data |
| 217 | UPDATED | 12/28/17 9:23 PM | Fun | Data |
| 217 | DELIVERED | 5/19/18 4:27 PM | Fun | Data |
| 227 | CREATED | 12/15/17 8:02 AM | Fun | Data |
| 227 | RESCHEDULED | 12/21/17 8:42 PM | Fun | Data |
| 227 | UPDATED | 12/28/17 9:27 PM | Fun | Data |
| 227 | RESCHEDULED | 1/16/18 1:50 PM | Fun | Data |
| 227 | UPDATED | 1/17/18 1:36 PM | Fun | Data |
| 227 | DELIVERED | 4/6/18 2:03 PM | Fun | Data |
| 227 | CREATED | 10/18/17 8:16 AM | Fun | Data |
| 227 | UPDATED | 12/28/17 9:45 PM | Fun | Data |
| 227 | RESCHEDULED | 1/1/18 4:40 PM | Fun | Data |
| 227 | UPDATED | 1/3/18 6:05 AM | Fun | Data |
| 227 | DELIVERED | 4/6/18 1:57 PM | Fun | Data |
In this case, keeping the same cutoff date of 5/15/2018, my expected result would be:
| ID | Type | Date | Other | Vars |
| 23 | UPDATED | 12/28/17 9:23 PM | Fun | Data |
| 32 | UPDATED | 12/31/17 9:10 PM | Fun | Data |
| 217 | UPDATED | 12/28/17 9:23 PM | Fun | Data |
| 227 | DELIVERED | 4/6/18 2:03 PM | Fun | Data |
Thanks for your help.
Hi,
You may download my PBI file from here. i have changed some data there to verify.
Hope this helps.
Ashish_Mathur wrote:Hi,
You may download my PBI file from here. i have changed some data there to verify.
Hope this helps.
Thank you so much for your help with this, though I have one additional question. Your solution is perfect for creating a visualization of the table I'm describing, but what If I want to make a visualization of the data in that table? I'm having trouble creating a table with this data that I can then use in other visualizations?
Hi,
I will not be able to help with that.