How to add colour background to a date

[WarrenBurton]

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. 

[iAM_BA]

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.

[WarrenBurton]

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]

Is there any specific rule for your formatting ?

[WarrenBurton]

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?

[AmiraBedh]

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

[WarrenBurton]

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.

[Syk · 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)

[WarrenBurton]

@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?

[Syk]

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!

[WarrenBurton]

@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?

[Syk]

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.

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

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

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.

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'.

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.

[WarrenBurton]

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!

 

[WarrenBurton]

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.