How do i apply conditional formatting for background colour in Powerbi based on date column updating by IF Statement

Question

The custom column created:

Date Test = SWITCH( True(), AND ( ISBLANK(oData_Activity_Dimension[Actual_Finish]), ISBLANK(oData_Activity_Dimension[Actual_Start]) ), (oData_Activity_Dimension[Schedule_Finish]), Isblank (oData_Activity_Dimension[Actual_Finish]), (oData_Activity_Dimension[Actual_Start]), IF((oData_Activity_Dimension[Actual_Finish]), (oData_Activity_Dimension[Actual_Finish]),(oData_Activity_Dimension[Schedule_Finish]) ))

this is pulling dates into a single column, based on if certain dates are populated. i need to apply colour coding to this. so that if actual start is entered ="yellow", actual finished entered = "green", schedule finish (no actual/schedule start) = "grey", and if the schedule finish goes over todays date = "red".

this doesnt seem to be something i can do with the conditional format - background colour options provided in PowerBI. can someone please provide a way to apply this to the column?

Answer 1

First of all there's an error in the IF, you haven't stated a logical test:

IF(
    (oData_Activity_Dimension[Actual_Finish]), 
    (oData_Activity_Dimension[Actual_Finish]),
    (oData_Activity_Dimension[Schedule_Finish]) 
)

When you've fixed that just have it output numbers rather than times. For instance have the double blank output 1, black finish as 2, etc.

Then under formatting you can set up a rule based on the measure and format the colors there.