Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
heatherkw
Helper I
Helper I

Return a Column Value Associated with a Max Date for a Client Within a Dynamic Date Range

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. 
2 REPLIES 2
heatherkw
Helper I
Helper I

I was able to figure this out via a SQL query. I'm dealing with another more complex issue related to this though, and may post a different question later! Thanks!

Ashish_Mathur
Super User
Super User

Hi,

Share some data to work with, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors