Conditional formatting based on differences between months in a matrix.

[sbm]

Hello Experts,

I want to highlight values based on differences between current months from Jan to Dec. If the value of Jan month is less than value of Feb month then want to highlight Jan value font in red and Feb value in green and so on for each year.

Thank you

[Anonymous · Accepted solution]

Hello @sbm 

This measure should do exactly what you are looking for:

Coloring =
VAR FXvalue = [Total Sales]
VAR FXLastMonth =
    CALCULATE (
        SUM ( FactTable[LineTotal] );
        PREVIOUSMONTH ( MasterCalendar[Date] )
    )
RETURN
    SWITCH ( TRUE (); FXvalue < FXLastMonth; "#ff0000"; "#90ee90" )

The measure evaluates the value with the value last month and if current month is lower make it red else make it green.

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

[Greg_Deckler · Accepted solution]

You will need a Previous Month measure and % Growth measure. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Assuming you have a measure called MyMeasure that is displaying your value in your matrix you would do something like:

PreviousMonth = CALCULATE([MyMeasure],PREVIOUSMONTH(Calendar[Date]))

% Growth = ([MyMeasure] - [PreviousMonth) / [PreviousMonth

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

You would then base your conditional logic on % Growth.

[sbm]

Thank you all for this solution.

[v-yingjl · Accepted solution]

Hi @sbm ,

You can create the following measure:

Measure =

var A = CALCULATE(SUM('table'[Sales]))

var B = CALCULATE(SUM('table'[Sales]),DATEADD('Calendar'[Date],-1,MONTH))

return

IF(A < B, "#ff0000","#90ee90")

Then in Matrix Format, choose conditional format:

, enable Background color and select ‘Advanced controls’:

,choose Format by Field value and select previous measure under Based on filed

Here is the demo , please try it:

PBIX 

Best Regards,

Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler · Accepted solution]

You will need a Previous Month measure and % Growth measure. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Assuming you have a measure called MyMeasure that is displaying your value in your matrix you would do something like:

PreviousMonth = CALCULATE([MyMeasure],PREVIOUSMONTH(Calendar[Date]))

% Growth = ([MyMeasure] - [PreviousMonth) / [PreviousMonth

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

You would then base your conditional logic on % Growth.

[Anonymous · Accepted solution]

Hello @sbm 

This measure should do exactly what you are looking for:

Coloring =
VAR FXvalue = [Total Sales]
VAR FXLastMonth =
    CALCULATE (
        SUM ( FactTable[LineTotal] );
        PREVIOUSMONTH ( MasterCalendar[Date] )
    )
RETURN
    SWITCH ( TRUE (); FXvalue < FXLastMonth; "#ff0000"; "#90ee90" )

The measure evaluates the value with the value last month and if current month is lower make it red else make it green.

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.