cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
apple1111
Helper II
Helper II

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

@apple1111 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
Super User
Super User

Hi @apple1111  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

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?


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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

apple1111_0-1674481273480.png

 

@apple1111 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

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Also another slight change

apple1111_1-1674485755774.png

 

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

 

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

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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

 

@apple1111 Is this it?
Screenshot_35.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

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!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors