Dynamic Status Based on Multiple Date Columns

[PowerBINoobie]

Hello, I am fairly new to PowerBi and hoping someone can help me with a problem I have been having. 

I have PartsTable with a Start Date, Process Date, Shipped Date, and Status columns. I also have a calendar table with multiple relationships defined to each date column in PartsTable(Im unsure if this is actually needed).

It is good to see an active status, but I want to see if possible a dynamic status. So for instance for part AAA status as of 3/1/2008 is shipped, but if I set a date different date range prior to 3/1/2008 and after 2/25/2008 it would appear as Processing. 

Any assitance would be appreaciated. Thank you. 

[brianheath]

Hi.. I am trying to replicate this for my own particular use but it does not seem to be updating.

I have created and linked the calander table and entered the code as proposed, but it seems to only pick up the first status regardless. Have I missed an important step somewhere?

Any pointers/suggestions or alternative ideas would be greatly valued.. 

Thanks in advance 

Brian 

[v-chuncz-msft · Accepted solution]

@PowerBINoobie,

You may create a measure as shown below.

Measure =
VAR d =
    MIN ( 'Calendar'[Date] )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( MAX ( PartsTable[Process Date] ) )
            || MAX ( PartsTable[Process Date] ) > d, "Started",
        ISBLANK ( MAX ( PartsTable[Shipped Date] ) )
            || MAX ( PartsTable[Shipped Date] ) > d, "Processing",
        "Shipped"
    )