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:

 

MONTHTICKETS UPDATED #STOCK REPLENISHED #INCREASED MPL / MDQ #
May 201994810
June 201911479
July 20198528

 

I would like to format as below:



I need to create a measure for each column, that will allow me to format as such:

 

IF( FOLLOWING MONTH Value > PREVIOUS MONTH Value , Green Color ,
IF( FOLLOWING MONTH Value < PREVIOUS MONTH Value , Red Color )) 

 

Help would be really appreciated.

 

Regards,

Robin

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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks mate, that's exactly what I'm looking for!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      You can optimise the above to be in one column (one go),
      I did it in 2 steps for better understanding the steps.

      Good Luck!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello, I tired thre abovce, but when I go to conditional formatting aand then select format by field the box for based on field is red and it wont let me choose the field I creatd above.

  • 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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

       

      I'm struggling with the MONTH column formatting.

      How do you get the MONTH column in acceptable date format for PARALLELPERIOD or PREVIOUSMONTH functions to work?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        All good, I found the function that transforms text to date: DATEVALUE()

        ALl sorted thanks guys :)