topic Re: Month on Month colour change in matrix in DAX Commands and Tips

Month on Month colour change in matrix

Ash2024 — Mon, 25 Mar 2024 17:06:09 GMT

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

Re: Month on Month colour change in matrix

lbendlin — Mon, 25 Mar 2024 23:56:12 GMT

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

Re: Month on Month colour change in matrix

FreemanZ — Tue, 26 Mar 2024 01:34:39 GMT

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/

Re: Month on Month colour change in matrix

Ash2024 — Tue, 26 Mar 2024 13:21:13 GMT

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

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.

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

Thank yo ufor any help.

Re: Month on Month colour change in matrix

Ash2024 — Tue, 26 Mar 2024 13:22:30 GMT

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.

Re: Month on Month colour change in matrix

lbendlin — Tue, 26 Mar 2024 20:02:06 GMT

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

Re: Month on Month colour change in matrix

Ash2024 — Wed, 27 Mar 2024 09:13:35 GMT

Hi Ibendlin,,

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

Re: Month on Month colour change in matrix

lbendlin — Wed, 27 Mar 2024 13:15:36 GMT

Access to what?

Re: Month on Month colour change in matrix

Ash2024 — Wed, 27 Mar 2024 13:18:35 GMT

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.

Re: Month on Month colour change in matrix

lbendlin — Wed, 27 Mar 2024 13:31:45 GMT

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-Forum/ba-p/963216

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/1447523

Re: Month on Month colour change in matrix

Ash2024 — Wed, 27 Mar 2024 14:00:25 GMT

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

Date of survey	Department	Type	Ease theme	Month
03/10/2023	Change	Standard	Fee	Oct-23
12/10/2023	Change	Standard	Fee	Oct-23
16/10/2023	Change	Standard	Fee	Oct-23
21/11/2023	Change	Standard	Fee	Nov-23
13/01/2024	Change	Plus	Fee	Jan-24
26/01/2024	Change	Plus	Fee	Jan-24
17/10/2023	Change	Standard	Service	Oct-23
23/10/2023	Change	Standard	Service	Oct-23
23/10/2023	Change	Standard	Service	Oct-23
03/11/2023	Change	Standard	Service	Nov-23
03/11/2023	Change	Standard	Service	Nov-23
06/11/2023	Change	Standard	Service	Nov-23
06/11/2023	Change	Standard	Service	Nov-23
06/11/2023	Change	Standard	Service	Nov-23
21/11/2023	Change	Standard	Service	Nov-23
04/12/2023	Change	Plus	Service	Dec-23
05/12/2023	Change	Plus	Service	Dec-23
06/12/2023	Change	Plus	Service	Dec-23
07/12/2023	Change	Plus	Service	Dec-23
07/12/2023	Change	Plus	Service	Dec-23
16/01/2024	Change	Plus	Service	Jan-24
17/01/2024	Change	Plus	Service	Jan-24
22/01/2024	Change	Plus	Service	Jan-24
22/01/2024	Change	Plus	Service	Jan-24
26/01/2024	Change	Plus	Service	Jan-24
26/01/2024	Change	Plus	Service	Jan-24
28/01/2024	Change	Plus	Service	Jan-24
29/01/2024	Change	Plus	Service	Jan-24
03/02/2024	Change	Plus	Service	Feb-24
07/02/2024	Change	Plus	Service	Feb-24
12/02/2024	Change	Plus	Service	Feb-24
14/02/2024	Change	Plus	Service	Feb-24
16/02/2024	Change	Plus	Service	Feb-24
17/02/2024	Change	Plus	Service	Feb-24
09/10/2023	Continue	Standard	Service	Oct-23
10/10/2023	Continue	Standard	Service	Oct-23
18/10/2023	Continue	Standard	Service	Oct-23
23/10/2023	Continue	Standard	Service	Oct-23
01/12/2023	Change	Standard	Price	Dec-23
13/12/2023	Change	Plus	Price	Dec-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?

Re: Month on Month colour change in matrix

Ash2024 — Wed, 27 Mar 2024 14:01:29 GMT

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

Re: Month on Month colour change in matrix

lbendlin — Wed, 27 Mar 2024 20:21:50 GMT

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