Forum Discussion
Conditional Formatting on Matrix - Comparing Values per Previous Month
Hi all,
Hoping to get some assistance - I have seen many posts that have similar issues, however, unable to get this to work myself.
I have a matrix with Months as the rows, and Accounts as the column. I have amounts for each account as the values. I would like to conditional format a cell colour as red if the value goes down compared to the previous month, and green if it goes up per the previous month.
I have provided an example below.
I have attempted to use the below code which I thought would work, however, was unlucky. Note that this was only for "red" lines, and also the colour used wasn't actually red, rather orange.
Colour =
SWITCH ( TRUE (),
CALCULATE ( SUM ( GLTLINA[GTL_AMOUNT] ) )
<
CALCULATE ( SUM ( GLTLINA[GTL_AMOUNT] ),
PREVIOUSMONTH ( GLTLINA[GTL_DATE] ) ),
"#ff8800")
Thanks in advance.
Mitch
Hi, Mitchell92
The dates in your original table are not consecutive, you need to create Measure 'color condition' based on a calendar table.
Please add a calendar table, replace the date fields with those in calendar table, and change ‘color condition’ as follows:
Color condition: = VAR _currentmonth = [Values total:] VAR _previousmonth = CALCULATE ( [Values total:], PREVIOUSMONTH('Calendar Table'[Date]) ) RETURN IF ( SELECTEDVALUE ( Sheet1[Date] ) = 1, "Black", IF ( _currentmonth < _previousmonth, "Green", "Red" ) )Best Regards,
Community Support Team _ Eason
13 Replies
Hi,
Because I do not have your sample data model, I could not write a measure that exactly suits your model. But I hope you can get a similar concept from the below measure and the attached pbix file to apply it to your sample.
Conditional formatting -> Font color -> Field value
Color condition: = VAR _currentmonth = [Values total:] VAR _previousmonth = CALCULATE ( [Values total:], FILTER ( ALL ( Data ), Data[Account] = MAX ( Data[Account] ) && Data[Month Number] = MAX ( Data[Month Number] ) - 1 ) ) RETURN IF ( SELECTEDVALUE ( Data[Month Number] ) = 1, "Black", IF ( _currentmonth > _previousmonth, "Green", "Red" ) )Hi JiHwan,
Thank you, I am much further as a result of your measure! I have run into two issues, one which was able to be resolved. Firstly, as I'm using accounting figures, numbers in brackets (i.e. less than 0) are negative (debits) and as a result, should be green. Given this fact, I have changed:
IF ( _currentmonth > _previousmonth, "Green", "Red" )To:
IF ( _currentmonth < _previousmonth, "Green", "Red" )I have run into the problem as per the attached screenshot, where The first line is an example of this measure working perfectly, the second line shows that the third column should actually be less (as current month is less than previous month) and the third line showing that the second column should be green (as it is less than previous month).
Wondering if you might know how to tweak the measure to make this 100% perfect? I think the issue lies when the numbers go below 0.
Thanks,
Mitch
Hi, Mitchell92
Please provide sanitized sample data that fully covers your question.
It will better help us understand the problem.
Best Regards,
Community Support Team _ Eason
