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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
A Year in Review - December 2024
Find out what content was popular in the Fabric community during 2024.
