Conditional Formatting on a Attribute/Value table

[peterg0417]

Hello all - I have a business request to conditionally format 2 date lines in a table and highlight the values red if we're within 90 days of the date. I tried with ChatGPT, but it couldn't get me there unfortunately. So I need actual human help 🙂

Here's a sample of my table as a Table visual:

Attribute	Formatted Value
Overall Project Duration (Months)	10 Months
Below Grade Construction
Above Grade Construction
Time to Substantial Performance	0 Months
Effective Date	2022-10-31
Expiration Date	2024-05-01
Owner (OCIP/CCIP)	Test
# of Floors	3
# of Parking Spaces	9
# of Suites	'-
Procurement Status	100%
Above Grade (SF)	10,339
Below Grade (SF)	'-
Total (SF)	10,339
Asset Class	Mixed-Use

Here's a sample of the unpivoted table in Power Query before being loaded:

Attribute	Value	DataType		Format	Value as Number
Overall Project Duration (Months)	10 Months	Project Info		null	null
Below Grade Construction		Project Info		null	null
Above Grade Construction		Project Info		null	null
End Date	2028-04-05	Project Info		null	null
Time to Substantial Performance	0 Months	Project Info		null	null
Effective Date	2022-10-31	Project Info		null	null
Expiration Date	2024-05-17	Project Info		null	null
Asset Class	Residential/Mixed-Use	Project Info		null	null
# of Floors	1	Project Info		#,##0;(#,##0)	1
# of Parking Spaces	4	Project Info		#,##0;(#,##0)	4
GCA Above Grade (SF)	10339	Project Info		#,##0;(#,##0)	10,339
Procurement Status	1	Project Info		#,##%;(#,##%)	1
GCA Total (SF)	10339	Project Info		#,##0;(#,##0)	10,339
Project Stage	Construction	Project Info		null	null
Start Date	2023-05-29	Project Info		null	null

And here's my existing DAX that is for the "Formatted Value" measure:

Formatted Value = 
VAR _Formatting =
    SELECTEDVALUE ( 'Project_Info - Unpivoted'[Format] )
RETURN
    SWITCH (
        TRUE (),
        OR (
            FIRSTNONBLANK (
                'Project_Info - Unpivoted'[Value],
                'Project_Info - Unpivoted'[Value]
            ) = "0",
            ISBLANK (
                FIRSTNONBLANK (
                    'Project_Info - Unpivoted'[Value],
                    'Project_Info - Unpivoted'[Value]
                )
            )
        ), "-",
        ISBLANK ( _Formatting ),
            FIRSTNONBLANK (
                'Project_Info - Unpivoted'[Value],
                'Project_Info - Unpivoted'[Value]
            ),
        FORMAT ( SUM ( 'Project_Info - Unpivoted'[Value as Number] ), _Formatting )
    )

What I need is the DAX query, in addition to the current formatting being applied, is check if the value for Expiration Date or Effective Date is within 90 days, and if it is, then highlight the font in red, for example.

[AnalyticsWizard]

To achieve the conditional formatting you want in Power BI for highlighting dates that are within 90 days of the current date, you'll need to use a combination of DAX for calculating the days left and Conditional Formatting in the table visual settings. Here’s a step-by-step process on how to set this up:

### Step 1: Calculate Days Remaining

First, create a new measure in your model that calculates the number of days from today until the Expiration Date and Effective Date.

```DAX
Days Until Expiration =
DATEDIFF(
TODAY(),
SELECTEDVALUE('Project_Info - Unpivoted'[Expiration Date]),
DAY
)

Days Until Effective =
DATEDIFF(
TODAY(),
SELECTEDVALUE('Project_Info - Unpivoted'[Effective Date]),
DAY
)
```

### Step 2: Adjust the Formatted Value Measure

You need to adjust your existing "Formatted Value" measure to include the logic for checking if the dates are within 90 days. Here's how you can modify your DAX formula:

```DAX
Formatted Value =
VAR _Formatting = SELECTEDVALUE('Project_Info - Unpivoted'[Format])
VAR _ExpirationDays = [Days Until Expiration]
VAR _EffectiveDays = [Days Until Effective]
VAR _Attribute = SELECTEDVALUE('Project_Info - Unpivoted'[Attribute])
RETURN
SWITCH (
TRUE(),
ISBLANK(_Formatting), FIRSTNONBLANK('Project_Info - Unpivoted'[Value], 'Project_Info - Unpivoted'[Value]),
OR (
FIRSTNONBLANK('Project_Info - Unpivoted'[Value], 'Project_Info - Unpivoted'[Value]) = "0",
ISBLANK(FIRSTNONBLANK('Project_Info - Unpivoted'[Value], 'Project_Info - Unpivoted'[Value]))
), "-",
(_Attribute = "Expiration Date" && _ExpirationDays <= 90) || (_Attribute = "Effective Date" && _EffectiveDays <= 90),
FORMAT(FIRSTNONBLANK('Project_Info - Unpivoted'[Value], 'Project_Info - Unpivoted'[Value]), _Formatting) & " - Highlight",
FORMAT(SUM('Project_Info - Unpivoted'[Value as Number]), _Formatting)
)
```

### Step 3: Apply Conditional Formatting in Power BI Visual

Now, apply the conditional formatting to the table visual:

1. **Select the table visual** you are using to display your data.

2. **Navigate to the conditional formatting options** for the column where your dates are displayed.

3. **Choose to format by rules** and select the field for which you have created the days remaining measure (like `Days Until Expiration`).

4. **Set the rules** such as "if less than or equal to 90" then set text color to red.

### Additional Considerations

- Ensure that your date fields (`Effective Date` and `Expiration Date`) are correctly formatted as dates in Power BI. This impacts how DATEDIFF calculates days.
- The highlighting text "- Highlight" in the DAX formula is just a placeholder to show how you could append additional text or symbols to highlight these entries. You can remove this or adjust it based on your needs, as the primary highlighting would be done via Conditional Formatting in the visual settings.

By following these steps, your Power BI report should now be able to dynamically highlight dates within 90 days from today in red, enhancing visibility for urgent or upcoming deadlines.

[peterg0417]

---

@AnalyticsWizard wrote:

### Step 3: Apply Conditional Formatting in Power BI Visual

Now, apply the conditional formatting to the table visual:

1. **Select the table visual** you are using to display your data.

2. **Navigate to the conditional formatting options** for the column where your dates are displayed.

3. **Choose to format by rules** and select the field for which you have created the days remaining measure (like `Days Until Expiration`).

4. **Set the rules** such as "if less than or equal to 90" then set text color to red.

---

Thank you for your help, but not sure this accomplishes what I need.

I need only the rows with the 2 dates (Effective Date and Expiration Date) to be red if they are less than 90 days away. Whereas this formatting changes the entire column to red, and I can only set the rules on 1 field (Days Until Effective OR Days Until Expiration)

[v-zhengdxu-msft]

Hi @peterg0417 

Althrough I've read your post once and once again, I haven't understand what is your expected outcome.

Could you please provide your expected result with backend logic and special examples? 

Best Regards

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

[peterg0417]

Attribute	Formatted Value
Overall Project Duration (Months)	10 Months
Below Grade Construction
Above Grade Construction
Time to Substantial Performance	0 Months
Effective Date	2022-10-31
Expiration Date	2024-05-01
Owner (OCIP/CCIP)	Test
# of Floors	3
# of Parking Spaces	9
# of Suites	'-
Procurement Status	100%
Above Grade (SF)	10,339
Below Grade (SF)	'-
Total (SF)	10,339
Asset Class	Mixed-Use

Please see "Expiration Date". The date is highlighted in red because it is within 90 days. I want to apply this conditional format within the existing DAX measure, "Formatted Value" that I provided above. For Expiration Date and Effective Date.

The issue is that  The values are all text, unless it's a number. If it's a number then I apply a custom format string to it, from the variable "_Formatting"

[v-zhengdxu-msft · Accepted solution]

Hi @peterg0417 

Thanks for your explaination, please try this:

First of all, I add a measure:

Measure = 
	VAR _DateofAttribute = SELECTEDVALUE('Table'[Attribute]) = "Effective Date" || SELECTEDVALUE('Table'[Attribute]) = "Expiration Date"
	VAR _DateDiff = IF(
		_DateofAttribute,
		ABS(DATEDIFF(
				DATEVALUE(SELECTEDVALUE('Table'[Formatted Value])),
				TODAY(),
				DAY
			))
	)
	RETURN
		IF(
			_DateDiff <> BLANK() && _DateDiff < 90,
			"Red"
		)

Then click the [Formatted Value] field and choose the Conditional formatting > Font color

Choose Field value and select the measure in the what field should we base this on? multi-check box.

The result is as follow:

Best Regards

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