Dynamically Retrieve Last Observation per ID

[topkatt]

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

[Ashish_Mathur]

Hi,

What result are you expecting?

[topkatt]

---

@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]

Hi,

I believe this is the result you want.  You may download the file from here.

Hope this helps.

[topkatt]

---

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  i have changed some data there to verify.

Hope this helps.

[topkatt]

---

@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?

[Ashish_Mathur]

Hi,

I will not be able to help with that.

[topkatt]

---

@Ashish_Mathur wrote:

Hi,

I will not be able to help with that.

---

Thanks for the feedback. Do you think this is something that isn't possible using Power BI? I'm wondering if my search for a solution is in vain.

[Seward12533]

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.

[Seward12533]

If you search through the forums this question is asked repeatedly. you need to use a caluclated COLUMN with Earlier

[topkatt]

---

@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?