Custom conditional formatting

[Justas4478]

Hi, I have this table that shows fulfillment for years over months.

I am trying to add conditional formatting that would highlith latest value in each month and if value is lower than previous value in same month it would be red and if it is highher it would be green, and if no change yellow, and if there is only one value in month it would not highligt it.
This is how end result would look like.

[danextian · Accepted solution]

Hi @Justas4478 

What exactly do you mean by the latest value for each month? So, if the most recent date with a value in December is the 31st and that value is 1, would you use that value? Then, would you compare it with the corresponding value from the previous month?

If you simply want the compare the value of this vs previous months value, try:

Conditional formatting color =
VAR _PrevMonth =
    CALCULATE ( [my measure], PREVIOUSMONTH ( datestable[date] ) )
VAR _Difference = [my measure] - _PrevMonth
VAR _Color =
    IF (
        NOT ( ISBLANK ( _PrevMonth ) ) && NOT ( ISBLANK ( [my measure] ) ),
        IF ( _difference > 0, "green", IF ( difference < 0, "red" ) )
    )
RETURN
    _Color

Select field value from conditional formatting option and use the measure above. You may replace red or green with RGB, RGBA or hexadecimal values.

[Justas4478]

@danextian I added additional IF statment to this part of dax to capture if value is = for sameperiodlastyear.

It looks to be working, but could you quickly check did I add it in correct part of dax code?

[Justas4478]

@danextian I tried you DAX but does not seam to return correct results.
This is what I get.

If I understand correctly how it works it is checking previous month in same year:
2023 August->September->October.
How I need it to check is same month of previous year
2023 August -> 2024 August.

[danextian]

Instead of PREVIOUSMONTH, use SAMEPERIODLASTYEAR. This compares 1/1/24 with 1/1/23 or Feb 2024 with Feb 2023. Please ensure that your dates table has been marked as such.

[Justas4478]

@danextian I just changed to SAMEPERIODLASTYEAR and it seams to have worked.
I still need to check in detail.

[Justas4478]

@danextian Unfortunatelly I cant do that, my data is using live connection model, so I am limited to measures only.
I can only see this and I dont know does that confirm what you were asking me to do.

[danextian]

It looks like the dates table has been marked as such as denoted by the  icon before the word date.

Try this:

Conditional formatting color =
VAR _PrevMonth =
    CALCULATE ( [my measure], SAMEPERIODLASTYEAR ( datestable[date] ) )
VAR _Difference = [my measure] - _PrevMonth
VAR _Color =
    IF (
        NOT ( ISBLANK ( _PrevMonth ) ) && NOT ( ISBLANK ( [my measure] ) ),
        IF ( _difference > 0, "green", IF ( difference < 0, "red" ) )
    )
RETURN
    _Color

[bhanu_gautam]

@Justas4478 , You will need to create a measure and use that in conditional formatiing

DAX
PreviousValue =
CALCULATE(
MAX('Table'[Value]),
FILTER(
'Table',
'Table'[Month] = EARLIER('Table'[Month]) &&
'Table'[Year] = EARLIER('Table'[Year]) &&
'Table'[Date] < EARLIER('Table'[Date])
)
)

ValueChange =
IF(
ISBLANK([PreviousValue]),
BLANK(),
IF(
[Value] > [PreviousValue],
"Green",
IF(
[Value] < [PreviousValue],
"Red",
"Yellow"
)
)
)

Use the ValueChange measure to apply the conditional formatting rules in the "Advanced controls" section.

https://www.youtube.com/watch?v=wTRrskQzAHk- go through this for more in depth

[Justas4478]

@bhanu_gautam My data is using Live connection model so I dont know if that prevents from use of EARLIER function.

[Justas4478]

@bhanu_gautam I tried creatign the emasure but for some reason it does not want to find my date table and columns.
This is what I get.