Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Reetz
Helper II
Helper II

Conditional formatting not working in a matrix

Hi All.  I have a matrix that shows Sales by month.  Dates are in the Columns and Product sales in the rows.  Our sales are always a couple of months behind so the most current month we have available is Jul'20.  I want to compare only July sales to June sales (current month to prior month).  If the current month is less than the prior month, I want to color just the current month's background red, otherwise, leave the background color as is and don't color any other cells in the row. 

 

In the example below, the first row would have no color in July since June and July are both blank.  In the 2nd row, the 4000 in July should be colored since it is less than the June sales.

Capture.PNG

  I've created the following measure to color code the cell:

 

m_Format Curr month =
VAR PriorMonthSales = CALCULATE( [Sales] , PARALLELPERIOD('Date'[Date],-1,MONTH ) )
VAR Compare = IF ( ISBLANK( PriorMonthSales ), BLANK(), [sales] - PriorMonthSales)
RETURN
SWITCH (
TRUE(),
Compare = 0, "none",
Compare < 0, "#ff7f7f",
Compare > 0, "none")
 
I tried conditional formatting the background by using the  Format by Field value but it won't let me add the measure in the Based on Field box.  I think it's because it is a measure.  What am I doing wrong?
 
Thanks in advance for your help!!
2 REPLIES 2
amitchandak
Super User
Super User

@Reetz , Try to use datesmtd or previousmonth

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))
Next to next  month value =  CALCULATE(sum('table'[total hours value]),nextmonth(dateadd('Date'[Date],1,MONTH)))


diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

 

on that, you can have your color measure. And use that measure with field option in conditional formatting

 

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Greg_Deckler
Super User
Super User

@Reetz - So generally you would return a numeric value in your measure and base your rules off of that and the color code is part of the rule creation, not part of the measure. Hope that makes sense.

 

Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.