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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
WarrenBurton
Frequent Visitor

How to add colour background to a date

Good day all.

 

This is a query regarding the ability to add a colour background (see the Excel spreadsheet) to a PowerBI report. How would I add a colour background to the PowerBI report?

 

Thank you in advance. 

 

Excel Screenshot.pngPowerBI Screenshot.png

1 ACCEPTED SOLUTION

Easiest way to do this (in my mind) is to have a 'Day Offset' column in your date table that is basically the #of days removed from the current day. Dates in the past will be negative, future will be positive. You can download the M script for a date table here if you'd like!

 

Once you have that created and related to your date column, all thats left is some conditional formatting rules. Under formatting > Cell Elements (make sure the 'Apply settings to' is the field you want to color > Background color (turn on and click fx)

Syk_0-1686229344337.png

 

Syk_1-1686229546380.png

 

View solution in original post

13 REPLIES 13
iAM_BA
Regular Visitor

Hi @WarrenBurton 

If there is any condition for particular date field you can add condition over there. 
Following link is might be useful for you. Follow that. 

https://hevodata.com/learn/power-bi-conditional-formatting/#:~:text=Select%20Power%20BI%20Conditiona....


Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Thank you for your reply @iAM_BA , yet I am unable to find a way to colour background for a date, only numbers or percentages. If I change the date to a number then perhaps it could work, but that would involve changing the number everyday. 

AmiraBedh
Resident Rockstar
Resident Rockstar

Is there any specific rule for your formatting ?


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Hi Amira

I have not yet set a rule for the formatting as I'm unsure how to do so with regards to using dates. As can be seen in the Excel spreadsheet, the dates with the green colour background are as of today or before today (not including blanks). What would you suggest?

So you want to color all the date values starting from today and the days before ?


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Yes, and when I change the date in the source file (Excel spreadsheet) the background colour hanges accordingly ie. yesterday was the 7th of June and included a green background; today is the 8th of June and should have a green background too. Am I making sense? In industry terms - the green background reflects a vessel arriving at a port.

Easiest way to do this (in my mind) is to have a 'Day Offset' column in your date table that is basically the #of days removed from the current day. Dates in the past will be negative, future will be positive. You can download the M script for a date table here if you'd like!

 

Once you have that created and related to your date column, all thats left is some conditional formatting rules. Under formatting > Cell Elements (make sure the 'Apply settings to' is the field you want to color > Background color (turn on and click fx)

Syk_0-1686229344337.png

 

Syk_1-1686229546380.png

 

@Syk My concern is that I'll have to adjust the days offset everyday (this is a report which I change everyday). Perhaps using a 'New Measure' to be automatically adjusted for the present day? What do you suggest?

The days offset column will update with a refresh of your data, the current day will always be 0. No need to touch it after initial setup!

@Syk Hi there...I'm not winning here. Being very much a newbie doing this, a lot of this is beyond me. How will I create the Days Offset column to reflect today as 0 when I refresh it? Is there not a New Measure function?

You need to create the date table in Power Query. Let's step through it.

1. In Power BI Desktop, under the Home tab click Transform data. This will open the Power Query Editor.

Syk_0-1686584166285.png

2. When it opens, hit New Source > Blank Query.

Syk_1-1686584538027.png


3. Now we will need the script for the date table. Follow this link and scroll down to the "Script" heading to click the link to download the script (just a .txt file). Then open it up in notepad or something similar to copy the whole thing (you also probably won't need all the columns here but you can delete them as you see fit).
https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query

4. Back in Power Query Editor, Click your blank query and then 'Advanced Editor'. This will open a new box for you to paste that script into. Replace any default text with the script and you should get something that looks like this. If you want to change what year it starts or ends at, set it here (I typically change my ToYear to be 2050 or something similar). Click Done

Syk_4-1686584965051.png

 

 

5. Rename your 'Query1' to 'Date' so you know exactly what it is and hit Close and Apply.

6. In the modeling view, locate your date table and drag the date field to your date field in the rest of your data. Your relationship should look like this.

Syk_5-1686585265274.png

 

7. Power BI wants to SUM your whole numbers in the date table so just click on the column here > (in Properties) expand the advanced option > Summarize by select 'None'.

Syk_6-1686585394578.png

 

8. Use this new date table as the date for any visualization you'd like to see as well as the slicer for your data. The Days Offset here will refresh every time you refresh your data to the current day.

Good luck! Let me know if you need more help.

 

 

 

Thank you @Syk 

I tried this with another date table with a days offset column and it sort of worked. Then I tried a new measure that I researched so that it will update to today everytime I refresh. Did the conditional formatting to include the field value (colour date). Gave me the same results as the date table/days offset method. You can see in the table that I am getting green background for all the columns but its not including all dates past and is also including some future dates. Then I thought it must be a relationship issue between the date table and the data. Am i doing something wrong in managing the relationship between the date table and the data? The relationships are between Date (in the Date Table) and each of (dates in the same format):

JEA WB ETA/ATA

MUN WB ETA/ATA

JAW WB ETA/ATA

CMB WB ETA/ATA

DUR WB ETD/ATD

TEM EB ETA/ATA

TIN EB ETA/ATA

APP EB ETA/ATA

DUR EB ETD/ATD

 

The main relationship exists between Date (in Date Table) and DUR WB ETD/ATD...my feeling is that its running everything off of this, as in the table the colour background is correct for the DUR WB ETD/ATD column only.  

 

Thank you for yout time!

 

 

PowerBI Screenshot5.png

 

 PowerBI Screenshot3.pngPowerBI Screenshot4.pngPowerBI Screenshot2.png

Hi @Syk , thank you for this. This seems to be the only way. I'm going to try this weekend and will see how it goes. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.