highlight actual date greater than forecasted date in a table

[Anonymous]

Hello,

I am fairly new to power bi, I am trying to create a conditional formatting rule in power bi where, if a date is less than another date in another column, then the cell gets highlighted in the table:

This is what I want my table visual to look like:

project id	forecasted date	actual date	measure
1234	Aug 21, 20		0
1345			0
1654	Feb 28, 21	May 10, 2018	1
1364	Aug 10, 16	June 18, 20	0
1476		Dec 31, 19	0

I created a measure that looks like this:

Measure = SWITCH(

    TRUE(),

    Table1[Forecasted Date]>ReportView[Actual Date],0,

    Table1[Forecasted Date]<ReportView[Actual Date],1,

    0)

the problem is, I know my measure is wrong because it is not taking into account the blank fields, I created the measure because I thought I could just do a simple conditional format based on this measure to highlight the actual date field where anything greater than 0 will be highlighted, but I am having a hard time with this. If someone could please help me figure this out, that would be much appreciated. Thank you! 

[v-cgao-msft · Accepted solution]

Hi @Anonymous ，

Please new a measure like:

Measure = 
IF(
    MAX(Table1[actual date])<MAX(Table1[forecasted date])
    && NOT ISBLANK(MAX('Table1'[actual date])),
    "Red"
)

Then select the table visual --> Visual--> Cell elements --> Apply settings to [forecasted date]-->font color 

result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

@v-cgao-msft  thank you! worked perfectly 🙂

[vicky_]

You can probably modify your switch statement to include another condition for NOT(ISBLANK(Table1[Forecasted Date]))

[Anonymous]

@vicky_  sorry how would that look like?

[vicky_]

I think something like this?

Measure = SWITCH(
    TRUE(),
    Table1[Forecasted Date]>ReportView[Actual Date],0,
    Table1[Forecasted Date]<ReportView[Actual Date] &&  NOT(ISBLANK(Table1[Forecasted Date])),1,
    0)

[Anonymous]

@vicky_  thank you 🙂