Project status duration

Hi lamacl01,

You get a wrong result because your values in the Status column will not return the correct result if you pick the maximum value, so we need to add some kind of statusID column, that can be used to sort the status's in the correct order.

As you can see in the screenshot below, I have added a Status table that also includes a statusId column. I have also calculated a new statusID column in my Table1 that I will need for the calculation. It's important that the relationship between the tables is set to 'Table1'[Status] --> 'Status'[Status] and not between the ID columns, hence you need to the relationship to calculate the statusID in Table1

I have calculated the 'Table1'[StatusID] with this formula: (edit: wrote Status instead of StatusID)

StatusID = 
CALCULATE( 
    VALUES( 'Status'[StatusID] ); 
    Table1[Status] = RELATED( 'Status'[Status] ) 
)

Then we need to change the formula for the "Status (MonthEnd)" Measure and first calculate the Maximun StatusID used on an item by the end of a given month and then use this value to calculate the Status that you want to see. My final measure looks like this now:

Status (MonthEnd) = 
VAR MaxUpdateDate = CALCULATE( MAX( Table1[Update Date] ); ALL( Period ) )
VAR MaxStatusId =
IF( 
    MIN( Period[Date] ) <= MaxUpdateDate;
    CALCULATE(
        MAX( Table1[StatusID] );
        FILTER(
            ALL( Period );
            Period[Date] <= MAX( Period[Date] )
        )
    )
)
RETURN
CALCULATE(
    VALUES( 'Status'[Status] );
    MaxStatusId = 'Status'[StatusID] 
)

And this is the result I get with your data:

Edit: I noticed something with your relationship to your calendar table - you have set Cross Filter Direction to "Both" - this should always be single unless you really need "Both".