Forum Discussion

PowerBINoobie's avatar
PowerBINoobie
Regular Visitor
9 years ago
Solved

Dynamic Status Based on Multiple Date Columns

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. 

  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

    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"
    )

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    9 years ago

    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"
    )
  • brianheath's avatar
    brianheath
    New Member
    4 years ago

    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