Conditional Formatting Date Cells based on Status and Current Date

[kuzya]

Hello Power Bi Community,

I need your support in determining if the following is possible using a table and conditional formatting in Power Bi.

I have a set of data which includes a reccord/event, multiple stages due dates, and the status/state of each event:

Is it possible to apply conditional formatting based on the current state of the event and compare to the legend on the right. For example, the first event is in final stage, that means that investigation, plan and implementation are complete (all 3 cells for dates should be green and that is the order to complete the process). For the current state, which is final, it should determine if the due date is >30 days (green), is between 30 and 15 (yellow), or is less than 15 days (red) in reference to today's date 5/30/2023. Becasue the final due date is greater than 30 days from today's date, the cell should be green.

In Event two, you can see that investigation and plan is complete (green), from todays date to the implementation date is 25 days, so based on the current status and current date, the cell should be yellow and so on.

Any insight is greately appreciated in how to resolve this question.

Thank you for your time.

[Anonymous · Accepted solution]

Hi @kuzya ,

Please create four measure with below dax formula:

Investigate Color =
VAR _state =
    SELECTEDVALUE ( 'Table'[State] )
VAR invest_date =
    SELECTEDVALUE ( 'Table'[Investigation Due Date] )
VAR _today =
    TODAY ()
VAR diff_date =
    DATEDIFF ( _today, invest_date, DAY )
VAR _a =
    SWITCH (
        TRUE (),
        diff_date < 15, "red",
        diff_date < 30
            && diff_date > 15, "yellow"
    )
VAR _val =
    SWITCH (
        _state,
        "Final", "green",
        "Plan", "green",
        "Investigation", _a,
        "Implementation", "green"
    )
RETURN
    IF ( ISBLANK ( invest_date ), BLANK (), _val )

Plan Color =
VAR _state =
    SELECTEDVALUE ( 'Table'[State] )
VAR plan_date =
    SELECTEDVALUE ( 'Table'[Plan Due Date] )
VAR _today =
    TODAY ()
VAR diff_date =
    DATEDIFF ( _today, plan_date, DAY )
VAR _a =
    SWITCH (
        TRUE (),
        diff_date < 15, "red",
        diff_date < 30
            && diff_date > 15, "yellow",
        diff_date > 30, "green"
    )
VAR _val =
    SWITCH (
        _state,
        "Final", "green",
        "Plan", _a,
        "Investigation", _a,
        "Implementation", "green"
    )
RETURN
    IF ( ISBLANK ( plan_date ), BLANK (), _val )

Imple Color =
VAR _state =
    SELECTEDVALUE ( 'Table'[State] )
VAR imple_date =
    SELECTEDVALUE ( 'Table'[Implementation Due Date] )
VAR _today =
    TODAY ()
VAR diff_date =
    DATEDIFF ( _today, imple_date, DAY )
VAR _a =
    SWITCH (
        TRUE (),
        diff_date < 15, "red",
        diff_date < 30
            && diff_date > 15, "yellow"
    )
VAR _val =
    SWITCH (
        _state,
        "Final", "green",
        "Plan", "green",
        "Investigation", _a,
        "Implementation", _a
    )
RETURN
    IF ( ISBLANK ( imple_date ), BLANK (), _val )

Final Color =
VAR _state =
    SELECTEDVALUE ( 'Table'[State] )
VAR final_date =
    SELECTEDVALUE ( 'Table'[Final Due Date] )
VAR _today =
    TODAY ()
VAR diff_date =
    DATEDIFF ( _today, final_date, DAY )
VAR _a =
    SWITCH (
        TRUE (),
        diff_date < 15, "red",
        diff_date < 30
            && diff_date > 15, "yellow",
        diff_date > 30, "green"
    )
VAR _val =
    SWITCH (
        _state,
        "Final", "green",
        "Plan", "green",
        "Investigation", _a,
        "Implementation", _a
    )
RETURN
    IF ( ISBLANK ( final_date ), BLANK (), _val )

Add a table visual with table fields and configure conditional format with measure:

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kuzya]

@Anonymous ,

This is great! Thank you so much for your support on this. I was able to align your code with my .pbix file and it works as intended. Something to note, I changed the colors to match conditional formating from excel. The hex colors are: Red "#FFC7CE", Yellow "#FFEB9C", and green "#C6EFCE". The output looks like this:

Also, as you can see in line 7 under Effectiveness Due Date, it should be yellow as is between 30 and 15 days from todays date (6/5/2023). To fix this, I am using the variable _a and it now reflects the appropriate format.

I will continue monitoring to determine if additional changes are needed.

Regards!