Forum Discussion
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 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.
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 _ColorSelect field value from conditional formatting option and use the measure above. You may replace red or green with RGB, RGBA or hexadecimal values.
10 Replies
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 _ColorSelect field value from conditional formatting option and use the measure above. You may replace red or green with RGB, RGBA or hexadecimal values.
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.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.
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 , 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
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.bhanu_gautam My data is using Live connection model so I dont know if that prevents from use of EARLIER function.
