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

[PBI_Curve]

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.

[v-shex-msft · Accepted solution]

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

[PBI_Curve]

@v-shex-msft worked a charm.

Thanks!