Forum Discussion
Dynamically Retrieve Last Observation per ID
Ashish_Mathur wrote:Hi,
What result are you expecting?
As an example, if I were to have the date slicer set to 5/15/2018, I would expect the data in my original post to look like this:
| ID | Type | Date | Other | Vars |
| 23 | UPDATED | 12/28/2017 9:23:01 PM | Fun | Data |
| 32 | UPDATED | 12/31/2017 9:10:58 PM | Fun | Data |
| 217 | UPDATED | 12/28/2017 9:23:01 PM | Fun | Data |
| 227 | DELIVERED | 4/6/2018 2:03:02 PM | Fun | Data |
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?