Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Conditional Formatting based on previous Value

Hi,   I have the above table as a Matrix in Power BI:   MONTH TICKETS UPDATED # STOCK REPLENISHED # INCREASED MPL / MDQ # May 2019 9 48 10 June 2019 11 47 9 July 2019 8 52 ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous 

    I did an example for 1 column.
    Create 2 new columns:

    Previous Month Tickets =
VAR prev_month =
    PREVIOUSMONTH ( T13[MONTH] )
RETURN
    IF (
        CALCULATE (
            SUM ( T13[TICKETS UPDATED #] ),
            FILTER (
                T13,
                prev_month = T13[MONTH]
            )
        )
            = BLANK (),
        0,
        CALCULATE (
            SUM ( T13[TICKETS UPDATED #] ),
            FILTER (
                T13,
                prev_month = T13[MONTH]
            )
        )
    )
    Colors =
IF (
    T13[TICKETS UPDATED #] > T13[Previous Month Tickets],
    "GREEN",
    "RED"
)

    Then format according to field value

     

    Cheers!
    A

jdbuchanan71's avatar
jdbuchanan71
Super User
7 years ago

Hello Anonymous 

You can do this with measures that compare the prior month amount to the current month amount the return a color string based on the compare.  I chose slightly lighter colors that the standard red and green.

Format Tickets Update = 
VAR PriorMonthAmt = CALCULATE( [TICKETS UPDATE #] , PARALLELPERIOD(Dates[Date],-1,MONTH ) )
VAR Compare = IF ( ISBLANK( PriorMonthAmt ), BLANK(), [TICKETS UPDATE #] - PriorMonthAmt)
RETURN 

SWITCH (
    TRUE(),
    Compare = 0, "none",
    Compare < 0, "#ff7f7f",
    Compare > 0, "#7fbf7f")
Format Stock Replenished = 
VAR PriorMonthAmt = CALCULATE( [STOCK REPLENISHED #] , PARALLELPERIOD ( Dates[Date],-1,MONTH ) )
VAR Compare = IF ( ISBLANK ( PriorMonthAmt ), BLANK(), [STOCK REPLENISHED #] - PriorMonthAmt)
RETURN 

SWITCH (
    TRUE(),
    Compare = 0, "none",
    Compare < 0, "#ff7f7f",
    Compare > 0, "#7fbf7f")
Format MPL / MDQ = 
VAR PriorMonthAmt = CALCULATE( [INCREASED MPL / MDQ #] , PARALLELPERIOD(Dates[Date],-1,MONTH ) )
VAR Compare = IF ( ISBLANK( PriorMonthAmt ), BLANK(), [INCREASED MPL / MDQ #] - PriorMonthAmt)
RETURN 

SWITCH (
    TRUE(),
    Compare = 0, "none",
    Compare < 0, "#ff7f7f",
    Compare > 0, "#7fbf7f")

Then you use the conditional formatting over the field