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,..
Solved! Go to Solution.
@apple1111 Is this it?
Hi @apple1111 Is this your desired outcome?
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
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
@apple1111 Can you provide your desired result?
Hi DimMD see two screen images of no max date and max date when all conditions are met
@apple1111 try it colum
MaxDate =
IF(
[ACTDate] = BLANK(),BLANK(),
CALCULATE( MAX('table (2)'[ACTDate]),
FILTER('table (2)', 'table (2)'[NUM] = MAX('table (2)'[NUM] ))))
DIMaMD -sorry not working getting a max date where Date Checj is zero as per image one
@apple1111 Please provide more input, everything is working correctly in my example
Also another slight change
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
@apple1111 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]
)
)
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
@apple1111 Is this it?
does your example return no max date when you have a 0 in date Check column. if yes can you provide a PBIX Please
@apple1111 yes
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
124 | |
73 | |
66 | |
53 | |
53 |
User | Count |
---|---|
199 | |
104 | |
88 | |
79 | |
77 |