Conditional Color Formatting based on date

[Anonymous]

Hi,

I have the following visualization using Matrix:

How do I color the background of the values under date for each dates if changed. Say first row data shows: the value changed from '4' to '5' for dates 09/19/2019 & 09/20/2019, 09/23/2019.

I want to change the background colors only if it has changed the values, otherwise should remain in same color for future results.

Thanks

[v-lili6-msft]

hi, @Anonymous 

For your case, just create a measure for conditional formatting as below:

Conditional = 
VAR _lastdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] < MAX ( 'Table'[Date] ) )
    )
RETURN
    IF (
        _lastdate <> BLANK (),
        IF (
            CALCULATE (SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] = _lastdate )) - CALCULATE ( SUM ( 'Table'[Value] ) ) = 0,  1,  2),
        1
    )

Then create the condtional formatting as below:

Result:

here is sample pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

Thanks for your solution. Can you please help me on this?

It seems to be highlighting every first occurences of the values for search_terms. Please find the screenshot: 

What I actually want is to to change the color only if the value has changed. For example:

Here the value of certain dates has changed from '3' to '4'. It has increased so it should be of different color(Red - Warning). If it remains same for next date, shouldn't change at all or no color. If it has decreased then it should be in different color(Green - Acceptable). I want to see the values of each search_terms is actually improving with numbers reducing for each dates or consistent. 

I hope I'm clear. 

Thanks

[v-lili6-msft · Accepted solution]

HI, @Anonymous 

The problem is that you are using SUM [Date] in the formula:

After my research on your sample pbix file, [Index] is text column, so you could try this formula:

Conditional = 
VAR _lastdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] < MAX ( 'Table'[Date] ) )
    )
RETURN
    IF (
        _lastdate <> BLANK (),
        IF (
            CALCULATE (MAX(  'Table'[Index] ), FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] = _lastdate )) - CALCULATE ( MAX( 'Table'[Index] ) ) = 0,  1,  2),
        1
    )

Result:

By the way, you could also adjust the formula as below so that if the value increase it will be red and if the value decrease it will be green.

Measure 2 = VAR _lastdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] < MAX ( 'Table'[Date] ) )
    )
RETURN
    IF (
        _lastdate <> BLANK (),
        IF (
            CALCULATE (MAX(  'Table'[Index] ), FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] = _lastdate )) - CALCULATE ( MAX( 'Table'[Index] ) )  <0,  3,  IF (
            CALCULATE (MAX(  'Table'[Index] ), FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] = _lastdate )) - CALCULATE ( MAX( 'Table'[Index] ) ) >0,2,
        1
    )),1)

Result:

and here is sample pbix file, please try it.

Best Regards,

Lin

[v-lili6-msft]

hi, @Anonymous 

Could you share your sample pbix file for me have a test? There should be something wrong in other.

You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin