Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
ArchStanton
Impactful Individual
Impactful Individual

Conditional Formatting

Hi,

Each month I am manually changing the font colour of the values below from red to black (red being future months).
I would like to do this automatically when we start a new month - how can I do this?

ArchStanton_0-1733219760389.png

 

My data calendar has a MonthSort columns as well as a month number, the month sort consists of 202401, 202402 and so on and my month sort is just 1 to 12.

The measures i am currently using for the 3 columns are:

 

Original Forecast = 
    VAR _firstdate = 
            CALCULATE(MIN('Total Closures'[Month]),
            FILTER('Modelling','Modelling'[Forecast] <> BLANK() )
            )
    RETURN
        CALCULATE(
                MIN('Modelling'[Forecast]),
                FILTER ('Modelling', Modelling[Month] = _firstdate)
        )

 

 

Actual incl Projected = SUM(Modelling[Actual & Projected])

 

 

% Difference2 = 
([Actual incl Projected]-[Original Forecast]) / [Last Value Forecast]

 

 

I'd be grateful for any advice

1 ACCEPTED SOLUTION

Hi, please follow these steps:
For table1 to see only future months, please create the following measure:

IsFutureMonth = 
IF(
    LASTDATE('Date'[Date]) > EOMONTH(TODAY(),0),
    1,
    0
)

 

For table2, to see only future and present month, please create the following measure:

IsFutureMonthPresent = 
IF(
    LASTDATE('Date'[Date]) >= EOMONTH(TODAY(),0),
    1,
    0
)

 

Now lets format your visual.

Select your table1, and in the Values field, right click on desired measure and add a conditional formatting as shown bellow:

Bibiano_Geraldo_0-1733310861327.png

 

In the prompt, in format style, choose Rules based on isFutureMonth measure, and color red when the value is 1, make sure to choose number and not percent, as shown bellow:

Bibiano_Geraldo_1-1733311080258.png

Repeat this steps for other values that you want to format in your matrix, and for you table2, just format using the measure isFuturePresent.

 

Your final result should look like this:

Bibiano_Geraldo_2-1733311196883.png

 

Download the sample: Case Modelling - Copy 

 

 

 

 

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

Thanks for the reply from Bibiano_Geraldo , please allow me to provide another insight:

Hi, @ArchStanton 

1.Firstly, I have created the following sample data based on your description:

vlinyulumsft_0-1733294300291.png

2.Secondly, I have created the following measure to determine whether the current date meets the requirements:

MEASURE =
IF (
    MONTH ( MAX ( 'Total Closures'[ Month] ) ) >= MONTH ( TODAY () )
        || YEAR ( MAX ( 'Total Closures'[ Month] ) ) > YEAR ( TODAY () ),
    "red",
    "black"
)

3.Next, modify the colours column by column in the position shown in the image below:

vlinyulumsft_1-1733294353987.png

vlinyulumsft_2-1733294353988.png

Note that every column in the visualization that you need to change color needs to be modified.

4.Here's my final result, which I hope meets your requirements.

vlinyulumsft_3-1733294366253.png

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, thanks for your reply.

Unfortunately its still not working.

 

My data model is a simple one:

ArchStanton_0-1733304940131.png

The Month column in my date table is Text = 

 

Month = FORMAT('Date'[Date], "MMM")

 

 

and so I get this error message with your measure:

ArchStanton_1-1733305025279.png

When I modify your measure to this then the formatting stays all black for the Original Forecast column:

 

Red or Black = 
IF (
    MONTH ( MAX ( 'Date'[Date] ) ) >= MONTH ( TODAY () )
        || YEAR ( MAX ( 'Date'[Date] ) ) > YEAR ( TODAY () ),
    "red",
    "black"
)

 

ArchStanton_2-1733305134529.png

 

Anonymous
Not applicable

Hi, @ArchStanton 

Thank you for your response.

 

As you suspected, we believe this issue might be caused by table relationships. We would appreciate it if you could provide your PBIX file so that we can tailor the solution based on your specific details.

 

We recommend uploading it to GitHub and sharing the link with us.When uploading a file, please be careful to delete sensitive information.

 

Looking forward to your reply.

 

Best Regards,

Leroy Lu

Hi,

Please find my pbix file link 

Test File 

 

Thanks

 

Bibiano_Geraldo
Super User
Super User

Hi @ArchStanton 
Assuming your MonthSort column contains values like 202401, 202402, etc., and your Month Number column contains values from 1 to 12, you can create a DAX measure as follows:

 

IsFutureMonth = 
VAR CurrentMonth = YEAR(TODAY()) * 100 + MONTH(TODAY())
RETURN
IF(MAX('Calendar'[MonthSort]) > CurrentMonth, 1, 0)

 

 

  • Go to the table visual where you want to apply the formatting.
  • Select the column you want to format.
  • Click on the dropdown arrow next to the column name and choose “Conditional formatting” > “Font color.”
  • In the conditional formatting pane, choose “Field value” and select the measure IsFutureMonth.
  • Set the color for 1 (future months) to red and 0 (current and past months) to black.

 

 

I tried this and there is no formatting at all in the Original Forecast column:

ArchStanton_1-1733225827657.png

ArchStanton_2-1733225887653.png

 

 

Thanks, unfortunately It won't accept anything in Field value:

ArchStanton_0-1733225595645.png

 

Hi, please follow these steps:
For table1 to see only future months, please create the following measure:

IsFutureMonth = 
IF(
    LASTDATE('Date'[Date]) > EOMONTH(TODAY(),0),
    1,
    0
)

 

For table2, to see only future and present month, please create the following measure:

IsFutureMonthPresent = 
IF(
    LASTDATE('Date'[Date]) >= EOMONTH(TODAY(),0),
    1,
    0
)

 

Now lets format your visual.

Select your table1, and in the Values field, right click on desired measure and add a conditional formatting as shown bellow:

Bibiano_Geraldo_0-1733310861327.png

 

In the prompt, in format style, choose Rules based on isFutureMonth measure, and color red when the value is 1, make sure to choose number and not percent, as shown bellow:

Bibiano_Geraldo_1-1733311080258.png

Repeat this steps for other values that you want to format in your matrix, and for you table2, just format using the measure isFuturePresent.

 

Your final result should look like this:

Bibiano_Geraldo_2-1733311196883.png

 

Download the sample: Case Modelling - Copy 

 

 

 

 

Awesome, thanks for all of your help with this👍

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.