Conditional Formatting Actuals vs. Forecast

Power BI
1

Does Power BI have the capability to conditional format actuals vs. forecast? For example, I’m wanting to color code all my actuals to blue and forecasts to green. I understand that there’s conditional formatting against a measurement but not a column–at least not that I know of.

2

@Max,

Check out the video below. If you’re using the Extended Date Table, you can leverage the ISAFTERTODAY field to dynamically distinguish between actuals and forecasts. I walk through an example of this in the video, and the same concept could be applied to using it for conditional formatting.

I hope this is helpful.

  • Brian
1 Like
3

Hi @Max, did the response content provided by @BrianJ help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

4

Thanks, Brian. Assuming I have the extended date table, do I just use the ISAFTERTODAY field in my measures? It looks like you created some variables but wondering if it’s as simple as just tacking that onto the measures.

5

@Max,

Re: your objective of conditional formatting, here’s how I would do it:

  1. Write a measure like this:

Cond Fmt =

    IF(
        SELECTEDVALUE( Dates[IsAfterToday] ) = TRUE,
        "Red",
        BLANK()
    )
  1. In the conditional formatting menu, select the column you want to format and field-based formatting. Set it up like this:

image
image934×324 9.63 KB

And boom! Conditional formatting updates dynamically with the date each time the report is refreshed:

image

I hope this is helpful.

  • Brian

P.S. shout out to @JarrettM. I used to think the field based formatting was the least useful option of the three, until I saw him use it like this, and now it’s my most frequently used method by far for conditional formatting, especially now that you can reference colors directly by name as opposed to just hex code .

3 Likes
6

Great—thanks, @BrianJ. I’m not near my computer right now but will try later and report back.

7

@BrianJ – it worked! Another question, is it possible to have the font color and background color different?

8

@Max,

Great – glad to hear that worked for you.

Sure – you can apply the exact same approach to font conditional formatting. Just develop a second measure nearly identical to the first called Cond Fmt Font, and instead of where I have “Red”, choose the font color you want. Then apply that same measure to the font conditional format option of that field, rather than the background format option.

  • Brian
1 Like
9

@BrianJ

Perfect. One last question, is it possible to conditionally format the columns. For example, it’s only formatting the values but I would also like it to format the column name (months) as well:
image

10

@Max,

That’s not something that’s directly doable within the current Power BI formatting options, but thankfully there are always clever people out there who’ve figured out how to trick Power BI into doing things it’s not directly capable of:

I hope this is helpful.

  • Brian
11

I see – I think I’ll stick to the simple value formatting :grimacing:

Thanks again, @BrianJ!

12

@Max,

I hear you. Just because you can do something in Power BI, doesn’t necessarily mean you should… :smiley:

  • Brian
1 Like
13

I addressed this a little different; though not too much

Actual / Budget filter = IF( LASTDATE( Dates[Date] ) <= TODAY() ,
1 , 0 )

The conditional formatting is the same. The above determines the colour based on the report reference to today.

Works well when doing a blended budget and actual report.