Calculated column - MAX Completion date based on 3 columns

[Anonymous]

Hi Experts

I am trying to work out the max completion date if all three conditions are met. See sample date 

I need to work out the latest work completion date Based on ID and NUM columns - the ACTDAte column must have dates against ID and NUM in order to get the MAX date.

As shown in our example data below we do not have a ACT date for ID 1257111 and NUM 346 - in this case new calcalated column return back blank as we have no ACT DAte for NUM 346 which is a part of ID 1257111, if we had ACT Dates then find the latest date in ACT DAte column and populate that into the new calculated column,..

[DimaMD]

Hi @Anonymous  Is this your desired outcome?

[Anonymous]

for each ID and Num grouping date we need to have a 1 in Date Check then return MaX date otherwise blank, as in the image above

[Anonymous]

Hi DimaMD the out come should be no date as we have missing entries for num  assoicated to ID - it need to be a complete set...the max if any need to be based on the 3 columns 

[DimaMD]

@Anonymous Can you provide your desired result?

[Anonymous]

Hi DimMD see two screen images of no max date and max date when all conditions are met

[DimaMD]

@Anonymous try it colum

MaxDate = 
 IF( 
    [ACTDate] = BLANK(),BLANK(),
     CALCULATE( MAX('table (2)'[ACTDate]),
         FILTER('table (2)', 'table (2)'[NUM] = MAX('table (2)'[NUM] ))))

[Anonymous]

DIMaMD -sorry not working getting a max date where Date Checj is zero as per image one

[DimaMD]

@Anonymous Please provide more input, everything is working correctly in my example

[Anonymous]

Also another slight change

[Anonymous]

Hi Dima MD - i modified your PBIX and see image the measure does not work now... NUM 344 should return zero max date but we have a value

[DimaMD]

@Anonymous Ok, then you just need to remove the IF condition

MaxDate = 
         CALCULATE(
            MAX('table (2)'[ACTDate]), 
                FILTER( 'table (2)',
                'table (2)'[NUM] = MAX('table (2)'[NUM]) && 'table (2)'[ID] = 'table (2)'[ID]
            )
        )

[Anonymous]

i DimaMD

We ahve no ACTDate and a zero in Date Check the MAx date should be blank() see image below not 29.09.21

[DimaMD · Accepted solution]

@Anonymous Is this it?

[Anonymous]

does your example return no max date when you have a 0 in date Check column. if yes can you provide a PBIX Please

[DimaMD]

@Anonymous yes