Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
1 year ago
Solved

Custom conditional formatting

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...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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's avatar
Justas4478
Post Prodigy
1 year ago

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's avatar
danextian
Super User
1 year ago

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's avatar
    Justas4478
    Post Prodigy
    1 year ago

    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's avatar
      danextian
      Super User
      1 year ago

      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
  • Justas4478's avatar
    Justas4478
    Post Prodigy
    1 year ago

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