How to color code current date in a Table

Power BI
1

Dear sir/ madam,

I would like to create a color code for current date in a Table format.

image

Based on the attached, is it possible to create a different color code for the current date?
In this case, 28 May is current date and I would like a different color background for it.
And it has to be dynamic as the date passes.

Thank you very much.
Sample Data for Current Date color code.pbix (40.9 KB)

2

Hi @Khaiboon,

Please see below.

To create a dynamic color code for the current date in a Power BI visual, you can follow these steps:

  1. Conditional Formatting:
  • In Power BI, you can use conditional formatting to dynamically change the background color based on the current date.
  • First, create a measure that calculates the current date using the TODAY() function. For example:
CurrentDate = TODAY()
  • Next, go to the visual where you want to apply the color change (e.g., a table, card, or matrix).
  • Select the field or cell that you want to format based on the current date.
  • In the “Format” section of the visual properties, find the “Conditional formatting” option.
  • Choose the “Background color” rule and set the condition to compare the field value (date) with the CurrentDate measure.
  • Define the color you want for the current date.
  1. Dynamic Color Expression:
  • If you want to use a custom color code (hex or RGB) for the current date, you can create a calculated column or measure that assigns the color based on the date.
  • For example, let’s say you want to use a different color for May 28th. You can create a measure like this:
BackgroundColor = IF([Date] = DATE(2024, 5, 28), "#FF5733", "#FFFFFF")

In this example, if the date matches May 28th, it will use the color #FF5733; otherwise, it will use white (#FFFFFF).
3. Apply the Color:

  • Finally, apply the BackgroundColor measure to the background color property of your visual element.

Remember to adjust the date and color codes according to your specific requirements. Power BI allows you to create dynamic visuals based on various conditions, so feel free to customize it further based on your needs!

Also get help from the Data Mentor within EDNA Platform.

I hope this helps.
Keith

3

Hi Keith,
I tried your method and I was not able to create the background color for current date using conditional formatting.

image
image936Ă—428 10.7 KB

I was not able to select the measures created for the selection.
I also tried the different Format style selection but to no avail.

I’m sorry for the trouble but can you help to create with the attached Power BI file?

Thanks again!

4

Hi @Khaiboon,

Sorry i don’t have time to create a file for you.

Are you using an EDNA date table? All project should have a date table.
Here are the links that Melissa have provide to the community.
Summary - Melissa - Enterprise DNA Forum
Extended Date Table (Power Query M function) - Power Query / M Code Showcase - Enterprise DNA Forum

Here is another option:

This is what i got from EDNA AI within Data Mentor

Creating Dynamic Color Formatting in Power BI

To create a different color code for the current date and make it dynamic as the date passes in a Power BI visual, you can follow these steps:

  1. Create a new column in your date table to identify the current date.
  2. Utilize conditional formatting in Power BI visualizations to apply different background color based on the current date.

Step 1: Create a New Column for Current Date

You can use DAX to create a new column in your date table that flags the current date. Suppose your date table is called DateTable and it includes a column Date, you can create a new column IsCurrentDate:

IsCurrentDate = IF(DateTable[Date] = TODAY(), 1, 0)

Copy

Step 2: Apply Conditional Formatting in Power BI

In your Power BI report:

  • Select the visual you want to apply the color formatting to.
  • Go to the formatting options of the visual.
  • Find the conditional formatting settings.
  • Choose the Background Color option.
  • Set a rule using the IsCurrentDate column you created.
  • Select the desired color for the current date.

By following these steps, you can create a Power BI visual where the background color changes dynamically for the current date.

Please have Data Mentor to help you too.

thanks
Keith

5

Hi Keith,
Thanks so much! I was able to make it work with your latest suggestion of using a date calendar!

Have a great day!

1 Like
6

Another option that doesn’t depend on having a date table in your semantic model (although you should) and uses a measure rather than a column: create two measures that return the foreground and background colors to use if a date in the table equals today, then use measures as the foreground and background for the table rows:

highlightTodayBG = 
VAR __d = max(Sales[Date])
RETURN 
IF(
    __d = TODAY()
    , "#738F7E"
    , "#FBF7F0"
)

highlightTodayFG = 
VAR __d = max(Sales[Date])
RETURN 
IF(
    __d = TODAY()
    , "#000000"
    , "#252423"
)

image
image280Ă—694 7.25 KB

1 Like
7

@HufferD 's solution is much better than the other ones that involved creating a column. The use-case scenario is a very simple one and does not account for creating additional columns in the model, there is no need.

Again, the Data Mentor suggestion is not on the optimal side, suggesting in my opinion, the worst solution. I refered to this issue in another post: https://forum.enterprisedna.co/t/does-ask-learn-work-as-expected/64255

8

Thank you David!
This is good too as we can use measures instead of calculated columns.

Cheers!