Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
@Anonymous Is this it?
Hi @Anonymous 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
@Anonymous Can you provide your desired result?
Hi DimMD see two screen images of no max date and max date when all conditions are met
@Anonymous 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
@Anonymous 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
@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]
)
)
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
@Anonymous 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
User | Count |
---|---|
134 | |
68 | |
68 | |
54 | |
52 |
User | Count |
---|---|
207 | |
95 | |
64 | |
61 | |
57 |