Forum Discussion

PBI_Curve's avatar
PBI_Curve
Helper I
7 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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