Get activity end date as the latest end date of the resources performing the activity

[David_Rolcy]

Hello everyone,

I need assistance with a DAX expression for Power BI. I've attached a picture illustrating the Current State (in green) and the Desired State (in green and orange). In the Current State, I have the start and end dates of resources performing certain activities. I want to add a column that displays the end date of each activity as the maximum end date of the employees performing that activity. 



I've attempted to use the following DAX expression (employing ALLEXCEPT to avoid circular errors), but it hasn't produced the desired outcome:

ActivityEndDate = 

CALCULATE(
    MAXX(
        FILTER(
            Activities,
            Activities[idActivity] = SELECTEDVALUE(Activities[idActivity])
        ),
        Activities[ResourceEndDate]
    ),
    ALLEXCEPT(Activities, Activities[Resource])
)

I've also included a link to a sample Excel file for reference.

https://docs.google.com/spreadsheets/d/1ertMCCWGi1800o52P46qQXk_9blFN1eG/edit?usp=drive_link&ouid=11... 

Your assistance with this matter would be greatly appreciated.

Thank you.

[David_Rolcy]

Hi @qqqqqwwwweeerrr . Thank you for  your help. I really appreciate it.
If possible, can you give me a further assistance? I thought I didn´t need to include the other piece (to not further complicate the problem) but I was mistaken. I want to use that new column in a matrix; so that if a resource is selected, the end date of the resource is selected; otherwise, the end date of the activity (the one we just got) is selected. Adding the resource column to your sample, I have the image below. I don´t know why it doesn´t work. Is there an alternative?

ActivityEndDate = 
IF( 
    NOT HASONEVALUE(Table_1[ResourceId]);
    CALCULATE(
        MAX(Table_1[ResourceEndDate]);
            FILTER(
                'table_1';
                table_1[ID] = EARLIER(table_1[ID])
            )
    );
    Table_1[ResourceEndDate]
)
//Note: my system uses semi-colon as separator; so that isn´t a source of error

Thank you again for you help @qqqqqwwwweeerrr 

[qqqqqwwwweeerrr]

Hi @David_Rolcy 

Apologies I am a bit confused. Here is my take if you want to use it in matrix

The use the measure not custom column reason being measure will be caluclated at run time (like if you have filter also it will work)

Second thing when you say resource is selected means in filter? or in row say if two resource is selected in filter then it should be activity date should be dislayed or if say only one selected then dispaly end of date like it will swtich between both based on resouce filter?
For that you can create swtich statment that will helfull

if this even does not helps please raise a new question in which please provide sample data in table form so that once can use direclty second expected output image, i will try to resolve coz it will be new learning to other as well and there might be chances that someon will give you better approach with solution 

Regards

[qqqqqwwwweeerrr · Accepted solution]

Hi @David_Rolcy 

I am not able to access your excel for some reason. I have taken sample data like below
you can create new column by this formula:

Column = CALCULATE(MAX(table_1[EndDate]),FILTER('table_1',table_1[ID] = EARLIER(table_1[ID])))

If you want dat fir the same here is the dax 

maxenddate = CALCULATE(MAX(table_1[EndDate]),REMOVEFILTERS(table_1[EndDate]))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards