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 |
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.