Conditional Formatting based on DATE and IS BLANK

[jcastr02]

I currently have a rule in place that if a data point is BLANK, then shade the table box RED.  I'd like to add another rule based on Class Start date.  If the class  start date is    MORE Than 7 days AGO, then shade red, if within the last 7 days, yellow.    

I'd like to combien both of those rules if blank and then the red/yellow combo

Please see screen shot.

[v-lid-msft]

Hi @jcastr02 ,

We can create a measure and set it as Field Value of Condition Format.

ColorFormat =
IF (
    TODAY () - MAX ( 'Table'[Class Start Date] ) <= 7,
    IF ( ISBLANK ( [YourMeasure] ), "#FFFF00" ),
    IF ( ISBLANK ( [YourMeasure] ), "#FF0000" )
)

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jcastr02]

@v-lid-msft 

What should I put for "Your Measure" would it be for the point value for each of the modules, meaning I would have create several quick measures?         I never created a measure for the ISBLANK rule - just did a rule in conditional formatting.  

see screen shot.

[v-lid-msft]

Hi @jcastr02 ,

Sorry for late reply, Can you find the Format by Field Value in the Condition Format? What the visual are you using?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jcastr02]

Yes, I can find it.  I am using a table visualization - just to clarify on the below..    What am I entering for yourmeasure?     My other rule is set as per below as well...

[v-lid-msft]

Hi @jcastr02 ,

We can create Different Measure for each column in table using formula like following

ColorFormatForModule1 =
IF (
    DATEDIFF ( MAX ( 'Table'[Class Start Date] ), TODAY (), DAY ) <= 7,
    IF ( ISBLANK ( SUM ( 'Table'[Module 1] ) ), "#FFFF00" ),
    IF ( ISBLANK ( SUM ( 'Table'[Module 1] ) ), "#FF0000" )
)

ColorFormatForModule2 =
IF (
    DATEDIFF ( MAX ( 'Table'[Class Start Date] ), TODAY (), DAY ) <= 7,
    IF ( ISBLANK ( SUM ( 'Table'[Module 2] ) ), "#FFFF00" ),
    IF ( ISBLANK ( SUM ( 'Table'[Module 2] ) ), "#FF0000" )
)

Then we set measure as the Field Value for each column in table visual.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jcastr02]

@v-lid-msft see attachment

Trying to update field value with DAX but boxes are not turning colors....see attachment

[v-lid-msft]

Hi @jcastr02 ,

Is the "M1 - STAR Overview" a measure? If it is a measure, Does is just sum the value of "Module 1" field?

Please also try to use the following measure as the Field Value of Conditional Format:

ColorFormatForModule1 =
IF (
    DATEDIFF ( MAX ( 'Table'[Class Start Date] ), TODAY (), DAY ) <= 7,
    IF ( SUM ( 'Table'[Module 1] ) + 0 = 0, "#FFFF00" ),
    IF ( SUM ( 'Table'[Module 1] ) + 0 = 0, "#FF0000" )
)

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jcastr02]

@v-lid-msft