Identifying last date by user and dept and display on user list

[lchaplen]

I have data for a program where I need to display the project and all the client detail.  One piece of information is to identify whether or not the client in the program also had services under another program and the last intake in this other program.  

I have the information for the first part and have started the second part.  I have clients and if they are in the program and created a column field if they are a EA Client and if not Other.  What I need to identify is the latest Date if they are a EA Client and then display that date.  So for C000041 I would need to display the date 9/30/2022 and for C003447 display 12/21/2022 in a new field.  If the client is not an EA Client then I would want the client to still display with no date. So no filtering  Due to personal ID information I can't attache the pbix file. If I were using SQL it would be easy but I can't figure it out in DAX. 

Data

Client_Code	Intake_Date	EA Client
C000041		Other
C000041	12/27/2012	EA Client
C000041	1/13/2014	EA Client
C000041	3/24/2017	EA Client
C000041	8/2/2017	EA Client
C000041	12/26/2018	EA Client
C000041	12/31/2019	EA Client
C000041	1/5/2021	EA Client
C000041	12/17/2021	EA Client
C000041	9/30/2022	EA Client
C000099		Other
C000099	6/18/2014	EA Client
C000099	5/5/2022	EA Client
C000099	9/30/2022	EA Client
C002104		Other
C002104	1/14/2013	EA Client
C002104	4/15/2014	EA Client
C002104	12/23/2014	EA Client
C002104	1/15/2016	EA Client
C002104	1/20/2022	EA Client
C002104	9/30/2022	EA Client
C003447		Other
C003447	9/5/2013	EA Client
C003447	2/20/2014	EA Client
C003447	4/3/2014	EA Client
C003447	11/19/2015	EA Client
C003447	8/29/2016	EA Client
C003447	8/13/2018	EA Client
C003447	5/14/2019	EA Client
C003447	12/21/2022	EA Client

[lchaplen]

HI I am close now.. this is the formula I"m using.  The issue is I still need to return Clients if they have no Intake date with for example a bogus date 1/1/1900 or ideally Blank.  

Any ideas anyone?

LastProgram =

Var ClientID = max('rpt vw_Client_Programs'[Client_Code])

//Var ClientProgram = 'rpt vw_Client_Programs'[EA Client)

return

 Calculate(

        LASTDATE('rpt vw_Client_Programs'[Intake_Date]),

     FILTER('rpt vw_Client_Programs','rpt vw_Client_Programs'[Client_Code] = ClientID && ('rpt vw_Client_Programs'[EA Client]= "Other" || 'rpt vw_Client_Programs'[EA Client]= "EA Client"

     ))

      )

This is my desired result:

Client_Code	Intake_Date	EA Client	Desired Result
C000041		Other	1/1/1900
C000041	12/27/2012 0:00	EA Client	9/30/2022
C000041	1/13/2014 0:00	EA Client	9/30/2022
C000041	3/24/2017 0:00	EA Client	9/30/2022
C000041	8/2/2017 0:00	EA Client	9/30/2022
C000041	12/26/2018 0:00	EA Client	9/30/2022
C000041	12/31/2019 0:00	EA Client	9/30/2022
C000041	1/5/2021 0:00	EA Client	9/30/2022
C000041	12/17/2021 0:00	EA Client	9/30/2022
C000041	9/30/2022 0:00	EA Client	9/30/2022
C003447		Other	1/1/1900
C003447	9/5/2013 0:00	EA Client	12/21/2022
C003447	2/20/2014 0:00	EA Client	12/21/2022
C003447	4/3/2014 0:00	EA Client	12/21/2022
C003447	11/19/2015 0:00	EA Client	12/21/2022
C003447	8/29/2016 0:00	EA Client	12/21/2022
C003447	8/13/2018 0:00	EA Client	12/21/2022
C003447	5/14/2019 0:00	EA Client	12/21/2022
C003447	12/21/2022 0:00	EA Client	12/21/2022

[Ashish_Mathur]

Hi,

Do you want a calculated column formula or a measure solution?  Also, clearly show the expected result.

[lchaplen]

Hi ideally a measure see Bolfri response.. that one is not working with error "A single value for column Client_Code in table Client_programs cannot be determined .  This can happen when a measure formula refers to column that contains many values without specifying an aggregation to get a single result

[Ashish_Mathur]

Hi,

I think a calculated column formula solution would work best.  This one does

Max date = if(Data[EA Client]="Other",BLANK(),CALCULATE(MAX(Data[Intake_Date]),FILTER(Data,Data[Client_Code]=EARLIER(Data[Client_Code])&&Data[EA Client]="EA client")))

[lchaplen]

Ashish, Thanks would it allow me to put a dummy date for the "Other" as I want all clients returned as not all will be an EA Client and I need to know that and return it.  

[lchaplen]

Ashish I tried: 

LastProgramDate = if('rpt vw_Client_Programs'[EA Client]="Other",Date(1900,01,01),CALCULATE(MAX('rpt vw_Client_Programs'[Intake_Date]),FILTER('rpt vw_Client_Programs','rpt vw_Client_Programs'[Client_Code]=EARLIER('rpt vw_Client_Programs'[Client_Code]))))  And it only filters for EA_Client = EA_Client.  

[lchaplen · Accepted solution]

Ok I figured it out.  I needed to create a column field

AssignProgDateifNone = if('rpt vw_Client_Programs'[EA Client]="Other",Date(1900,01,01),'rpt vw_Client_Programs'[Intake_Date])

to assign a date for "Other".  

Then I created a new column: 

LastProgramDate = CALCULATE(MAX('rpt vw_Client_Programs'[Intake_Date]),FILTER('rpt vw_Client_Programs','rpt vw_Client_Programs'[Client_Code]=EARLIER('rpt vw_Client_Programs'[Client_Code])))

And this got me the results I needed.  

IMHO this is a poor PowerBI design compared to a SQL solution.. Just my 2 cents.

[bolfri]

Because its not a measure. Its calculated column.

[lchaplen]

Ok thanks I just posted an update.. any ideas to get this result?

[bolfri]

Client date = 
VAR current_client = 'Sample'[Client_Code]
VAR EA_Flag = 'Sample'[EA Client]
RETURN
    CALCULATE (
        LASTDATE ( 'Sample'[Intake_Date] ),
        FILTER (
            'Sample',
            'Sample'[Client_Code] = current_client
                && 'Sample'[EA Client] = EA_Flag
        )
    )

Result:

[lchaplen]

HI thanks I'm getting the error:  "A single value for column Client_Code in table Client_programs cannot be determined . This can happen when a measure formula refers to column that contains many values without specifying an aggregation to get a single result