Conditional Formatting based on previous Value

[Anonymous]

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	8

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

[jdbuchanan71]

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]

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]

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

ALl sorted thanks guys 🙂

[Anonymous · Accepted solution]

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]

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.

[Anonymous]

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

[Anonymous]

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!