cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jcastr02
Post Prodigy
Post Prodigy

Conditional Formatting based on DATE and IS BLANK

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. cond. formatting.png

8 REPLIES 8
v-lid-msft
Community Support
Community Support

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

6.PNG

 

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.

 

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.

@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.rule.png

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.

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.

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...

color format.png

 

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

3.PNG

 

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

 

4.PNG

 

5.PNG

 


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.

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.

@v-lid-msft see attachment

 

Trying to update field value with DAX but boxes are not turning colors....see attachmentcolor format DAX.png

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.

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.