Forum Discussion
Dynamically Retrieve Last Observation per ID
Hi All,
I'm trying to create a table that has the last observation for each ID in my data, relative to the current value in a date slicer.
Sample data below, thanks for your help!
| ID | Type | Date | Other | Vars |
| 23 | CREATED | 10/23/2017 7:39:30 AM | Fun | Data |
| 23 | UPDATED | 12/28/2017 9:23:01 PM | Fun | Data |
| 23 | DELIVERED | 5/18/2018 4:27:10 PM | Fun | Data |
| 32 | CREATED | 10/31/2017 10:56:25 AM | Fun | Data |
| 32 | UPDATED | 12/31/2017 9:10:58 PM | Fun | Data |
| 32 | DELIVERED | 6/16/2018 4:26:31 PM | Fun | Data |
| 71 | CREATED | 5/22/2018 4:14:09 PM | Fun | Data |
| 71 | CANCELED | 5/22/2018 5:19:00 PM | Fun | Data |
| 217 | CREATED | 11/29/2017 7:41:00 AM | Fun | Data |
| 217 | UPDATED | 12/28/2017 9:23:01 PM | Fun | Data |
| 217 | DELIVERED | 5/19/2018 4:27:45 PM | Fun | Data |
| 227 | CREATED | 12/15/2017 8:02:19 AM | Fun | Data |
| 227 | RESCHEDULED | 12/21/2017 8:42:59 PM | Fun | Data |
| 227 | UPDATED | 12/28/2017 9:27:24 PM | Fun | Data |
| 227 | RESCHEDULED | 1/16/2018 1:50:29 PM | Fun | Data |
| 227 | UPDATED | 1/17/2018 1:36:26 PM | Fun | Data |
| 227 | DELIVERED | 4/6/2018 2:03:02 PM | Fun | Data |
| 227 | CREATED | 10/18/2017 8:16:00 AM | Fun | Data |
| 227 | UPDATED | 12/28/2017 9:45:29 PM | Fun | Data |
| 227 | RESCHEDULED | 1/1/2018 4:40:54 PM | Fun | Data |
| 227 | UPDATED | 1/3/2018 6:05:40 AM | Fun | Data |
| 227 | DELIVERED | 4/6/2018 1:57:48 PM | Fun | Data |
11 Replies
If you search through the forums this question is asked repeatedly. you need to use a caluclated COLUMN with Earlier
Seward12533 wrote:If you search through the forums this question is asked repeatedly. you need to use a caluclated COLUMN with Earlier
I honestly have combed through these forums, and have found similar questions but none that seem to answer this specific issue. If you happen to know of one, do you mind pointing me to it?
Hi,
What result are you expecting?
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 For this you want a disconnected slicer to harvest the date value for the target date. You can due this by usins the VALUES function to create a table (NEW TABLE form the modeling tab) DateSelection = VALUES(date[date]) but don't relate it to other tables in you model. thenuse SELECTEDVALUE in a measure to harvest the user choice or define a default (would recommend MAX date from your fact table. SELECTEDDATE = SELECTEDVALIUE(DateSelection[DATE},MAX(table[Date])
Then add a column to that compare the date of the row to the SELECTEDDATE and then use the Matrix/Table visual filters to only include the rows where the check column you added is true.
