I have found similar solutions to my problem online, but am not quite finding the exact scenario that I need to solve. I need unduplicated client counts across a few categories, but the clients can have values in multiple categories throughout the year, so they are showing up multiple times and my totals aren't adding up correctly.
Essentially what I want to do is count the number of records where an exit type meets certain criteria, based on the last exit date for that client within a dynamic date range. I can identify the last exit date within a dynamic range, but I can't figure out how to return the exit type associated with that last date for each client in order to do unduplicated counts.
Here is my calculation for the last exit date:
LastExit =
VAR MinDate = FIRSTDATE('Date'[Date])
VAR MaxDate = LASTDATE('Date'[Date])
VAR _lastIndex =
CALCULATE(MAX(ePROD[ExitDate90Days]), ALLEXCEPT(ePROD, ePROD[PersonalID]),
ePROD[ExitDate90Days] >= MinDate,
ePROD[ExitDate90Days] <= MaxDate,
ePROD[Perspective] = "Client",
ePROD[ShelterHL] = "Include")
RETURN
CALCULATE (
SELECTEDVALUE(ePROD[ExitDate90Days]),
ALL(ePROD),
ePROD[ExitDate90Days] = _lastIndex)
What I really want to do instead is count the number of distinct personal ids where ExitTypeDetail = "Permanent Housing" and the exit date is equal to _lastIndex, and then I want to repeat this for ExitTypeDetail = "Inactive" and ExitTypeDetail = "Left System". Each client should only be counted once in one of these three categories based on the exit type associated with their last exit within the specified timeframe. I will be graphing this over time, so each client could have a _lastIndex associated with every year and this is why I need it to be dynamic and not just based on a max date for each client.
