cancel
Showing results for
Did you mean:
Regular Visitor

## Conditional Formatting - One Date's Values Based Another Date's Value

Hi Guys,

I'm creating a fairly basic table listing sales totals per salesperson, over time - as below. There are 3 fields: Date, Sales and Salesperson, that make up this table.

What I would like to do is colour code increases/decreases/no changes on the most recent date's total based off the previous date's total.

 01-Jan 02-Jan Tom £  800.00 £  700.00 Jane £  400.00 £  400.00 Mary £           - £  150.00 John £  200.00 £  150.00 Luke £  650.00 £  800.00

So for example, the above would be [coloured] as below:

 01-Jan 02-Jan Tom £  800.00 £700.00 [RED] Jane £  400.00 £400.00 [YELLOW] Mary £           - £150.00 [GREEN} John £  200.00 £150.00 [RED] Luke £  650.00 £800.00 [GREEN]

I can only seem to apply conditional formatting to all values (i.e. both columns) on a general highest/lowest scale.

Is it possible to do what I'm looking for?

Cheers.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Conditional Formatting - One Date's Values Based Another Date's Value

Hi @PBI_Curve,

I'd like to suggest you write a measure to calculate the diff between date and return color string based on result, then you can simply use above measure as conditional format field of value column.

Measure =
VAR currPerson =
SELECTEDVALUE ( 'Table'[Person] )
VAR currDate =
MAX ( 'Table'[Date] )
VAR prevDate =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( ALLSELECTED ( 'Table' ), [Date] < currDate )
)
VAR diff =
LOOKUPVALUE (
'Table'[Amount],
'Table'[Person], currPerson,
'Table'[Date], currDate
)
- LOOKUPVALUE (
'Table'[Amount],
'Table'[Person], currPerson,
'Table'[Date], prevDate
)
RETURN
IF ( diff > 0, "Green", IF ( diff = 0, "Orange", "Red" ) )

Regards.

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
2 REPLIES 2
Community Support Team

## Re: Conditional Formatting - One Date's Values Based Another Date's Value

Hi @PBI_Curve,

I'd like to suggest you write a measure to calculate the diff between date and return color string based on result, then you can simply use above measure as conditional format field of value column.

Measure =
VAR currPerson =
SELECTEDVALUE ( 'Table'[Person] )
VAR currDate =
MAX ( 'Table'[Date] )
VAR prevDate =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( ALLSELECTED ( 'Table' ), [Date] < currDate )
)
VAR diff =
LOOKUPVALUE (
'Table'[Amount],
'Table'[Person], currPerson,
'Table'[Date], currDate
)
- LOOKUPVALUE (
'Table'[Amount],
'Table'[Person], currPerson,
'Table'[Date], prevDate
)
RETURN
IF ( diff > 0, "Green", IF ( diff = 0, "Orange", "Red" ) )

Regards.

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Regular Visitor

## Re: Conditional Formatting - One Date's Values Based Another Date's Value

@v-shex-msft worked a charm.

Thanks!

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 119 members 1,613 guests
Recent signins: