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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculated column - MAX Completion date based on 3 columns

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,..

 

apple1111_0-1674476743812.png

 

 

 

1 ACCEPTED SOLUTION

@Anonymous Is this it?
Screenshot_35.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

View solution in original post

15 REPLIES 15
DimaMD
Solution Sage
Solution Sage

Hi @Anonymous  Is this your desired outcome?
Screenshot_31.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Anonymous
Not applicable

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
Not applicable

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?


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Anonymous
Not applicable

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

apple1111_0-1674481273480.png

 

@Anonymous try it colum

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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Anonymous
Not applicable

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
Screenshot_33.jpg
Screenshot_34.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Anonymous
Not applicable

Also another slight change

apple1111_1-1674485755774.png

 

Anonymous
Not applicable

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_0-1674485604487.png

 

@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]
            )
        )

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Anonymous
Not applicable

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_0-1674487457392.png

 

@Anonymous Is this it?
Screenshot_35.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Anonymous
Not applicable

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

@Anonymous yes 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.