Conditional Formatting Dax

[callumbradshaw]

I know there is a lot of stuff out there about this but I have been going at it for ages and still can't figure it out.
I am trying to compare each months count with the previous month. I want to colour the background red if the count in month was less than previous and green if the count was greater than the previous month.

I have this measure: 

but it just has an output matrix:

[Alex87]

did you create the date table starting the first day of the year until the last day of the year? did you create relationship based on day date? I will sent you my pbix in the next couple of hours if you still can't solve the issue

[callumbradshaw]

I am still getting the same result. 
I have now created a date table as you suggested and have created a link in the data model between the date table and table I am visualising in the matrix. I can't think of what could be the problem. Do you have any thoughts?

[Alex87]

I am not alowed to upload a pbix. So please follow the instructions below:

1. Create the date table in Power query using the link I sent. Choose full years for the data to be generated

2. Mark the table as date table

3. Create a relationship between Dates Date and Month in your data table

4. Create a new measure DAX with the code provided

Matrix Object Color = 

VAR _CurrentMonthResult = SUM(DataConD[Value])
VAR _PrevPer = SELECTEDVALUE(Dates[CurrMonthOffset]) - 1
VAR _PreviousMonthResult = 
    CALCULATE(
        SUM(DataConD[Value]), 
              FILTER(ALL(Dates),
               Dates[CurrMonthOffset] = _PrevPer)
               )
VAR _Result = IF(_CurrentMonthResult < _PreviousMonthResult, "#F6EEEF", "#F0F6EE")

RETURN
_Result

5. Create a matrix as below

6. Apply conditional format based on the measure DAX (right click on Values - cond format - background color)

7. Mark my answer as the solution. Thanks!

[callumbradshaw]

I am still getting the same result, which is a table full of green values. I followed your steps from creating a scratch report too. Not sure why this isn't working. 

The join between the Date table and my data table is probably the sticking point. What data type is your month?

When I create a table with just the dates, I get this: (where Date is the column in the date table and the other three columns are from my data table)

[Alex87]

Relationships have the same data type, which is Date. 

[Anonymous]

Hi @callumbradshaw ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[callumbradshaw]

Thank you for your repsonse! I am still getting the same output as before. 

I have created a column in my date table with Current Month Offset as that is what I thought you did above.

And this is my new measure:

Do you know why this is happeneing?

[Alex87]

You need to use a proper date calendar. the Month offset you created is not good.

I recommend using Melissa's PQ date table, by far the most adanced calendar table out there.

Extended Date Table (Power Query M function) - Power Query / M Code Showcase - Enterprise DNA Forum

For info, my month offset looks like this:

[Alex87]

Hello,

I reproduced your usecase

The formula I used for conditional formatting is the following:

Matrix Object Color =

VAR _CurrentMonthResult = SUM(DataConD[Value])

VAR _PrevPer = SELECTEDVALUE(Dates[CurrMonthOffset]) - 1

VAR _PreviousMonthResult =

    CALCULATE(

        SUM(DataConD[Value]),

              FILTER(ALL(Dates),

               Dates[CurrMonthOffset] = _PrevPer)

               )

VAR _Result = IF(_CurrentMonthResult < _PreviousMonthResult, "#F6EEEF", "#F0F6EE")

RETURN

_Result

Replace the sum of value from my example with the count of ids as in your datasource and it should work.

If it does work, do not forget to mark my post as a solution. Thanks!