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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Ash2024
Regular Visitor

Month on Month colour change in matrix

Hello,

 

I'm trying to figure out if I'm able to change the colour in the coloumns for month on month variations.

 

Ash2024_1-1711386223719.png

 

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

1 ACCEPTED SOLUTION

lbendlin_0-1711570897734.png

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")

View solution in original post

12 REPLIES 12
FreemanZ
Super User
Super User

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:

FreemanZ_0-1711416695225.png

 

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

Ash2024_0-1711459074773.png

 

I've done step one (after the date) - 

Theme_total = SUM('Ease verbatim'[Count])
 
Then for this step - 
color =
VAR _premonth = FORMAT(EDATE(MAX('Test dates'[Date]), -1), "MMM/YY")
VAR _presales =
    CALCULATE(
        [Theme_total],
        'Test dates'[MMM-YY] = _premonth
)
VAR _sales = [Theme_total]
VAR _result =
IF(
    _presales<>BLANK(),
    IF(_sales>_presales, "Red", "Green")
)
RETURN _result
 
Do I need to amend where you've entered sales/presales?
 
I'm looking at month on month for the theme, this is what I return, after adding colour to the conditional formatting. 
 
Ash2024_1-1711459218693.png

So, I know I'm doing something wrong, I'm just wondering if it is that part.

 

Thank yo ufor any help.

lbendlin
Super User
Super User

Your data model contains a calendar table, right?  What should the color be for the Total?

Hi Ibendlin,

 

I do, for total I would like that to stay black. So, just the change in colour for the data.

 

I've entered some additional information in a reply above.

Please provide sample data that fully covers your issue. No screenshots please.
Please show the expected outcome based on the sample data you provided.

Hi Ibendlin,,

 

I don't have access to do this, unfortunately.

Access to what?

Hi Ibendlin, 

 

As a new member, I'm not able to upload my data set. I'm not able to share this via my work laptop either, in any other format. I would like to upload my pbi file, but I can't do this, as there's no option.

 

So, I'm only able to send the screenshots that are above.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thanks Ibendlin, I'll copy the tables below from excel -

 

 

Date of surveyDepartmentTypeEase themeMonth
03/10/2023ChangeStandardFeeOct-23
12/10/2023ChangeStandardFeeOct-23
16/10/2023ChangeStandardFeeOct-23
21/11/2023ChangeStandardFeeNov-23
13/01/2024ChangePlusFeeJan-24
26/01/2024ChangePlusFeeJan-24
17/10/2023ChangeStandardServiceOct-23
23/10/2023ChangeStandardServiceOct-23
23/10/2023ChangeStandardServiceOct-23
03/11/2023ChangeStandardServiceNov-23
03/11/2023ChangeStandardServiceNov-23
06/11/2023ChangeStandardServiceNov-23
06/11/2023ChangeStandardServiceNov-23
06/11/2023ChangeStandardServiceNov-23
21/11/2023ChangeStandardServiceNov-23
04/12/2023ChangePlusServiceDec-23
05/12/2023ChangePlusServiceDec-23
06/12/2023ChangePlusServiceDec-23
07/12/2023ChangePlusServiceDec-23
07/12/2023ChangePlusServiceDec-23
16/01/2024ChangePlusServiceJan-24
17/01/2024ChangePlusServiceJan-24
22/01/2024ChangePlusServiceJan-24
22/01/2024ChangePlusServiceJan-24
26/01/2024ChangePlusServiceJan-24
26/01/2024ChangePlusServiceJan-24
28/01/2024ChangePlusServiceJan-24
29/01/2024ChangePlusServiceJan-24
03/02/2024ChangePlusServiceFeb-24
07/02/2024ChangePlusServiceFeb-24
12/02/2024ChangePlusServiceFeb-24
14/02/2024ChangePlusServiceFeb-24
16/02/2024ChangePlusServiceFeb-24
17/02/2024ChangePlusServiceFeb-24
09/10/2023ContinueStandardServiceOct-23
10/10/2023ContinueStandardServiceOct-23
18/10/2023ContinueStandardServiceOct-23
23/10/2023ContinueStandardServiceOct-23
01/12/2023ChangeStandardPriceDec-23
13/12/2023ChangePlusPriceDec-23

 

 

I have a date table in Power BI. 

 

I mainly use this on my date table -

 

Month = EOMONTH('Date'[Date],-1)+1
 
Month-YY = FORMAT('Date'[Month],"mmm-yy")
 
Would you need anything more?

I had to remove lines in the table, so feel free to add more data in department and ease theme to get more data.

lbendlin_0-1711570897734.png

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

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors