Forum Discussion

Sweet-T's avatar
Sweet-T
Helper III
7 years ago
Solved

Conditional formatting matrix: Comparing cells to adjacent value

Hello everyone -    I'd like to conditionally format the cells of a matrix to indicate whether or not sales are trending up or down, compared to the previous time period. I say time period, because...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Sweet-T

    I continue to work on this requirement, and today I find a way may achieve your requirement.

    Sorry about my carelessness.

     

    Now, this is a new way for you refer to;

    basic data

    Step1:

    Add a Year Quarter Number column

    Year Quarter Number = YEAR ( Table1[Date] ) * 100 + INT ( FORMAT ( [Date], "q") )

    Step2:

    Add this measure

    %change = 
VAR previousDate =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Qty] > 0
                && Table1[Date] < MAX ( Table1[Date] )
        )
    )
VAR previousYQ =
    CALCULATE (
        MAX ( Table1[Year Quarter Number] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Qty] > 0
                && Table1[Year Quarter Number] < MAX ( Table1[Year Quarter Number] )
        )
    )
VAR previousREG =
    CALCULATE (
        SUM ( Table1[Qty] ),
        FILTER ( ALLSELECTED ( Table1 ), Table1[Date] = previousDate )
    )
VAR previousYQTOTAL =
    CALCULATE (
        SUM ( Table1[Qty] ),
        FILTER ( ALLSELECTED ( Table1 ), Table1[Year Quarter Number] = previousYQ )
    )
VAR previousQty =
    IF (
        ISFILTERED ( Table1[Date].[Quarter] ),
        previousYQTOTAL,
        IF ( ISFILTERED ( Table1[Date].[Month] ), previousREG )
    )
RETURN
    DIVIDE ( SUM ( Table1[Qty] ) - previousQty, previousQty, 0 )

    Step3:

    Add Conditional formatting for matrix

     

    Result:

    here is pbix, please try it.

    https://www.dropbox.com/s/j3sraf440cbjs13/Conditional%20formatting%20for%20matrix.pbix?dl=0

     

    Best Regards,

    Lin

     

     

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, Sweet-T

For your first requirement, it can be achieved by adding a measure like: PREVIOUSMONTH Function/PREVIOUSQUARTER Function/PREVIOUSYEAR Function then set Conditional formatting like it.

for example:

 

Qty QoQ% = 
IF (
    ISBLANK ( CALCULATE ( SUM ( Table1[Qty] ), PREVIOUSQUARTER ( 'Date'[Date] ) ) ),
    BLANK (),
    CALCULATE ( SUM ( Table1[Qty] ) )
        - CALCULATE ( SUM ( Table1[Qty] ), PREVIOUSQUARTER ( 'Date'[Date] ) )
)

 

It works well.

 

  

Best Regards,

Lin