Forum Discussion
Reetz
Helper II
6 years agoConditional 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 availabl...
amitchandak
Super User
6 years agoReetz , 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-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values