Hello,
I'm trying to figure out if I'm able to change the colour in the coloumns for month on month variations.
For example, above I'd like to be Green, red, green, red, green. So, it shows green for when the value drops and red for an increase.
The values for my test are in as "Ease verbatim".
I can't seem to see how to do this in conditional formatting.
Any assistance would be great.
Thank you
color =
var cv = COUNTROWS('Table')
var mm = max('Table'[Month])
var pm = CALCULATE(max('Table'[Month]),'Table'[Month]<mm)
var pv = CALCULATE(countrows('Table'),'Table'[Month]=pm)
return SWITCH(TRUE(),ISBLANK(pv),BLANK(),cv>pv,"red","green")
Hi @Ash2024 ，
not sure if i fully get you. supposing you have a data table like:
| Date | Product | Sales |
| 1/1/2024 | A | 1 |
| 1/21/2024 | A | 1 |
| 2/10/2024 | A | 1 |
| 3/1/2024 | A | 1 |
| 3/21/2024 | A | 1 |
| 1/1/2024 | B | 1 |
| 1/21/2024 | B | 1 |
| 2/10/2024 | B | 10 |
| 3/1/2024 | B | 10 |
| 3/21/2024 | B | 10 |
try to:
1) add a calculated dates table like:
dates =
ADDCOLUMNS(
CALENDAR(MIN(data[Date]), MAX(data[Date])),
"YY/MM", FORMAT([Date], "YY/MM")
)2) relate data[date] with dates[date]
3) plot a matrix visual with data[product], dates[yy/mm], and a simple measure like:
Salesttl = SUM(data[Sales]) 4) In the matrix, apply conditional formating for [Salesttl] with a measure like:
color =
VAR _premonth = FORMAT(EDATE(MAX(dates[date]), -1), "YY/MM")
VAR _presales =
CALCULATE(
[Salesttl],
dates[yy/mm] = _premonth
)
VAR _sales = [Salesttl]
VAR _result =
IF(
_presales<>BLANK(),
IF(_sales>_presales, "Red", "Green")
)
RETURN _result
it worked like:
More about conditional formatting:
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting
https://powerbidocs.com/2020/11/02/conditional-formatting-by-field-value-in-power-bi/
Hi FreemanZ,
Thank you for this. I'm still quite new to power BI, so I've tried this, but I'm not getting the colour change. I'm wondering if it's due to me not knowing if I need to amend where you have sales / presales.
I can't seem to be able to add my test pbi here. So, hopefully these will help -
A snip of my test date
I've done step one (after the date) -
So, I know I'm doing something wrong, I'm just wondering if it is that part.
Thank yo ufor any help.
color =
var cv = COUNTROWS('Table')
var mm = max('Table'[Month])
var pm = CALCULATE(max('Table'[Month]),'Table'[Month]<mm)
var pv = CALCULATE(countrows('Table'),'Table'[Month]=pm)
return SWITCH(TRUE(),ISBLANK(pv),BLANK(),cv>pv,"red","green")
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
