Select Most Recent Client Record Given Date Values Selected

[SDream7]

Hello everyone,

I've been having trouble finding a solution for this problem, having not been able to replicate solutions posted to similar problems featured on the forums.

In the link below, you'll be able to download my dummy dataset.

https://my.syncplicity.com/share/dbtnt5bxh0stupg/Dummy%20Data

Essentially, I have a client table that lists a given client's "Client ID" , the "Program" the client entered, the corresponding dates of "Admission" and "Discharge" into said program, etc. Each row is therefore a record of client admissions into a given program. All clients are assigned a unique Client ID, that's used across all programs these clients happen to enter.  Clients will appear in many programs, multiple times across different time periods. Hence, Client IDs will appear multiple times throughout the Client Table. 

Here are the columns:

Client ID

Program

Admission Date / Project Start Date

Discharge Date

Client Status

RS StatuS

Ultimately, I want the Client Table, based on both the Admission and Discharge Dates selected, to show the latest record for each Client ID.  I don't care about what program the client was in, but rather what was the lastest record. Therefore, the Client Table should de-duplicate based on "Client ID", given the Admission and Discharge Dates selected.  In addition to the Admission and Discharge Date columns being used as slicers, "Client Status" and "RS Status" will also be made into slicers, therefore de-duplication should take these into account as well. 

How I'd like for all this to work is through DAX calculated columns. I want a calculated column (something like a "Latest Record - Yes or No") to run through the table, determining whether a given row counts as the latest record for that Client ID, given the admission/discharge date slicers selected, along with whatever slicer value selected on the "Client Status" and "RS Status" slicers.

The tricky part is that I want the Admission and Discharge Date slicers to show clients who were "in program" during the date range created when selecting an individual Admission and Discharge Date. So for example, if I selected an Admission Date of 01/01/2017 and a Discharge Date of 12/31/2017, a client record that has both an admission date of 09/01/2016 and a discharge date of 03/03/2017 should still remain on the Client Table since this record was active during the 01/01/2017-12/31/2017 time period selected.  Given this rule and any other slicer values selected, I want the Latest Record calculated column to determine which rows should count as a client's latest record.  Lastly, the Latest Record calculated column should be capable of being used as a slicer as well.

[v-juanli-msft]

Hi @SDream7

To show the latest record for each Client ID based on both the Admission and Discharge Dates selected, you could create these measures.

(i can't find any rows with both lastest Admission and Discharge Dates as the slicer show.

based on the slicer, lastest Admission and Discharge Dates are 2017/6/14 and 2018/5/15, but there isn't any row with  2017/6/14 and 2018/5/15 for Admission and Discharge Dates meanwhile

So i assume show the lastest Admission date based on the lastest Discharge Dates, this is to mean for the lastest Discharge Dates 2018/5/15, show the lastest Admission date 2017/5/28)

max of Admission Date = CALCULATE(MAX([Admission Date / Project Start Date]),ALLSELECTED(Sheet1))

max of Discharge Date = CALCULATE(MAX([Discharge Date]),ALLSELECTED(Sheet1))

based on last discharge daate = CALCULATE(MAX([Admission Date / Project Start Date]),FILTER(ALLSELECTED(Sheet1),[Discharge Date]=[max of Discharge Date]))

last record = IF(MAX([Admission Date / Project Start Date])=[based on last discharge daate]&&MAX([Discharge Date])=[max of Discharge Date],1,0)

Best Regards

Maggie

[v-juanli-msft]

Hi @SDream7

I want the Client Table, based on both the Admission and Discharge Dates selected, to show the latest record for each Client ID

this can be done by creating a measure instead of a calculated column  for calculated columns can't change with the slicer.

"the Latest Record calculated column should be capable of being used as a slicer as well"

To make the Latest Record change based on the slicer, it should be a measure, so it can't be used as a slicer.

I believe the requirements from your statements can be achieved except creating the Latest Record as a calculated column.  

Best Regards

maggie

[SDream7]

Thank you for your response. How might I go about creating what I want into measures?

[SDream7]

Bump