Forum Discussion
Background color when date falls between 30 days
Hello
I have table to display last gate date and next gate date in the table. I need to highlight the background with blue color when next gate falls in next 45 days from today.
How can we write a DAX program
Hi manojk_pbi
You can’t directly set background color in DAX, but you can create a measure that returns a color code and then apply it via conditional formatting in the table visual.
Step 1: Create a DAX measure
NextGateColor =
VAR NextGateDate = SELECTEDVALUE('YourTable'[Next Gate Date])
VAR TodayDate = TODAY()
RETURN
IF(NOT ISBLANK(NextGateDate) && NextGateDate >= TodayDate && NextGateDate <= TodayDate + 45, "#ADD8E6", // Light Blue "#FFFFFF" // White (no highlight))Step 2: Apply Conditional Formatting
1.In your Power BI table visual:
Click the dropdown on Next Gate Date → Conditional formatting → Background color.
2.Choose Format by → Field value.
3.Select NextGateColor measure.
This will highlight the background in blue for rows where Next Gate Date is within 45 days from today, and keep others white.
Regards,
Akhil.
4 Replies
Hi manojk_pbi
You can’t directly set background color in DAX, but you can create a measure that returns a color code and then apply it via conditional formatting in the table visual.
Step 1: Create a DAX measure
NextGateColor =
VAR NextGateDate = SELECTEDVALUE('YourTable'[Next Gate Date])
VAR TodayDate = TODAY()
RETURN
IF(NOT ISBLANK(NextGateDate) && NextGateDate >= TodayDate && NextGateDate <= TodayDate + 45, "#ADD8E6", // Light Blue "#FFFFFF" // White (no highlight))Step 2: Apply Conditional Formatting
1.In your Power BI table visual:
Click the dropdown on Next Gate Date → Conditional formatting → Background color.
2.Choose Format by → Field value.
3.Select NextGateColor measure.
This will highlight the background in blue for rows where Next Gate Date is within 45 days from today, and keep others white.
Regards,
Akhil.You can create a visual calculation which will give difference between last gate date and next gate date.then using cell elements option you can apply conditional formatting
Hi manojk_pbi ,
Were you able to try the conditional formatting approach with the color measure? Did it highlight the rows with Next Gate Date within 45 days as expected?
Regards,
Akhil.Hi manojk_pbi ,
Circling back were you able to test the conditional formatting approach using the color measure? Did it highlight the rows with Next Gate Date within 45 days as expected? If it worked, that’s great, but if you’re still seeing issues, sharing a quick screenshot or example will help us pinpoint the problem.
Regards,
Akhil.
