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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mitchell92
Helper I
Helper I

Conditional Formatting on Matrix - Comparing Values per Previous Month

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.

 

MatrixExample.PNG

 

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

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

 

9.png

Best Regards,
Community Support Team _ Eason

View solution in original post

13 REPLIES 13
Anonymous
Not applicable

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

Jihwan_Kim
Super User
Super User

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

 

Picture1.png

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

ResultExample.PNG

 

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

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

 

 

Hi,

 

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

 

Thanks,

 

Mitch

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

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

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

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

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

Hello,

 

Sorry for the delay, please shared find link below:

 

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

 

Thanks,

 

Mitch

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

 

9.png

Best Regards,
Community Support Team _ Eason

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.