Conditional Formatting on Matrix - Comparing Values per Previous Month

[Mitchell92]

Hi all,

Hoping to get some assistance - I have seen many posts that have similar issues, however, unable to get this to work myself.

I have a matrix with Months as the rows, and Accounts as the column. I have amounts for each account as the values. I would like to conditional format a cell colour as red if the value goes down compared to the previous month, and green if it goes up per the previous month.

I have provided an example below.

I have attempted to use the below code which I thought would work, however, was unlucky. Note that this was only for "red" lines, and also the colour used wasn't actually red, rather orange.

Colour = 
SWITCH ( TRUE (),
 CALCULATE ( SUM (  GLTLINA[GTL_AMOUNT] ) ) 
<
 CALCULATE (  SUM ( GLTLINA[GTL_AMOUNT] ),
        PREVIOUSMONTH ( GLTLINA[GTL_DATE] )    ),
 "#ff8800")
        

Thanks in advance.

Mitch

[eugin]

Can we apply the same logic to compare between weeks?Can you please help me on this.

[Jihwan_Kim]

Hi,

Because I do not have your sample data model, I could not write a measure that exactly suits your model. But I hope you can get a similar concept from the below measure and the attached pbix file to apply it to your sample.

Conditional formatting -> Font color -> Field value

Color condition: =
VAR _currentmonth = [Values total:]
VAR _previousmonth =
    CALCULATE (
        [Values total:],
        FILTER (
            ALL ( Data ),
            Data[Account] = MAX ( Data[Account] )
                && Data[Month Number]
                    = MAX ( Data[Month Number] ) - 1
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( Data[Month Number] ) = 1,
        "Black",
        IF ( _currentmonth > _previousmonth, "Green", "Red" )
    )

[Mitchell92]

Hi JiHwan,

Thank you, I am much further as a result of your measure! I have run into two issues, one which was able to be resolved. Firstly, as I'm using accounting figures, numbers in brackets (i.e. less than 0) are negative (debits) and as a result, should be green. Given this fact, I have changed:

IF ( _currentmonth > _previousmonth, "Green", "Red" )

 To:

IF ( _currentmonth < _previousmonth, "Green", "Red" )

I have run into the problem as per the attached screenshot, where The first line is an example of this measure working perfectly, the second line shows that the third column should actually be less (as current month is less than previous month) and the third line showing that the second column should be green (as it is less than previous month).

Wondering if you might know how to tweak the measure to make this 100% perfect? I think the issue lies when the numbers go below 0.

Thanks,

Mitch

[v-easonf-msft]

Hi, @Mitchell92 

Please provide sanitized sample data that fully covers your question.

It will better help us understand the problem.

Best Regards,
Community Support Team _ Eason

[Mitchell92]

Hi,

I can't seem to upload a report example? Its saying "The file type (.pbix) is not supported...

Thanks,

Mitch

[v-easonf-msft]

Hi, @Mitchell92 

You can upload the sample .pbix to cloud storage, set permissions to public, and share the link here.

Best Regards,
Community Support Team _ Eason

[Mitchell92]

Hi,

I'm not sure how do do that without sharing my full name and company name. Is it not possible for me to upload the document directly onto this website?

Thanks,

Mitch

[v-easonf-msft]

Hi, @Mitchell92 

I‘m not sure what you mean by 'without sharing my full name and company name'.

It is suggested to unpack your real-life problem into a simple case that does not contain sensitive data..

Alternatively, you can consider sharing relevant screenshots (relevant tables, matrix fields, measure formulas).

Best Regards,
Community Support Team _ Eason

[Mitchell92]

Hi,

I mean if I share a link to you via Cloud Storage, it will include my full name and company name.

I have created a dummy pbix that does not contain sensitive data however am unable to load it. Would you per chance be able to suggest a Cloud Storage I could use outside of my organisation?

Apologies, I know this is a mission.

Thanks,

Mitch

[v-easonf-msft]

Hi, @Mitchell92 

Dropbox, One Drive, Google Drive can all be your way of sharing. It's up to you. 

What you need to ensure is that sensitive information is not leaked.

If you still have some doubts about sharing, Google search can be one of the best teachers you can have.

If it is really inconvenient, you can share the screenshot directly.

Best Regards,
Community Support Team _ Eason

[Mitchell92]

Hello,

Sorry for the delay, please shared find link below:

https://app.box.com/s/9xt6zosqylkq9mj71rtt8fdysfcz4bt1

Thanks,

Mitch

[v-easonf-msft · Accepted solution]

Hi, @Mitchell92 

The dates in your original table are not consecutive, you need to create Measure 'color condition' based on a calendar table.

Please add a calendar table, replace the date fields with those in calendar table, and change ‘color condition’ as follows:

Color condition: = 
VAR _currentmonth = [Values total:]
VAR _previousmonth =
        CALCULATE (
        [Values total:],
       PREVIOUSMONTH('Calendar Table'[Date])
    )
RETURN
    IF (
        SELECTEDVALUE ( Sheet1[Date] ) = 1,
        "Black",
        IF ( _currentmonth < _previousmonth, "Green", "Red"
    )
)

Best Regards,
Community Support Team _ Eason

[Mitchell92]

This is perfect, thank you so much for your support and patience!