Forum Discussion

Mitchell92's avatar
Mitchell92
Helper II
4 years ago
Solved

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 t...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

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's avatar
    Mitchell92
    Helper II
    4 years ago

    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's avatar
      v-easonf-msft
      Community Support
      4 years ago

      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's avatar
        Mitchell92
        Helper II
        4 years ago

        Hi,

         

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

         

        Thanks,

         

        Mitch