Forum Discussion
Conditionally Format Matrix Column based on Date
Hello,
I am attempting to create a matrix visual of sales data, comparing Current Year over Prior Year, by Week. I would like to be able to highlight current week. I have a column in my dataset that flags the current week number of "5". I cannot figure out why I cannot conditionally format the value, based on the week_number being equal to current_week_number.
In this example below, I would like to highlight column "5".
Anonymous add a measure for highlight and then use field value in conditional formatting to highlight.
Current Week Highlight = VAR __currentWeekNumber = CALCULATE ( MAX ( Table[WeekNumber] ), Table[CurrentWeekFlag] = "Yes" ) RETURN IF ( MAX ( Table[WeekNumber] ) = __currentWeekNumber, "Red" )Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
2 Replies
Anonymous add a measure for highlight and then use field value in conditional formatting to highlight.
Current Week Highlight = VAR __currentWeekNumber = CALCULATE ( MAX ( Table[WeekNumber] ), Table[CurrentWeekFlag] = "Yes" ) RETURN IF ( MAX ( Table[WeekNumber] ) = __currentWeekNumber, "Red" )Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Thank you so much!
I added to it, to turn it into a SWITCH statement. This is because I have about 7 different customers that run on different fiscal calendars. If there is a better way to handle this, let me know. Otherwise, it's working as intended. Thanks again!
Current Week Highlight = SWITCH(TRUE(), VALUES('Demand Group'[DEMAND_GROUP])="TSC",VAR __currentWeekNumber = CALCULATE ( MAX ('D_DATES (All)'[TSC_FISCAL_WEEK_NBR] ), 'D_DATES (All)'[CAL_DATE] = VALUES('D_DATES (All)'[TSC LAST QOH DATE])) RETURN IF (MAX ('D_DATES (Weekly)'[TSC_FISCAL_WEEK_NBR] ) = __currentWeekNumber, "Yellow" ), VALUES('Demand Group'[DEMAND_GROUP])="LOWES",VAR __LCWNumber = CALCULATE (MAX ('D_DATES (All)'[LOWES_FISCAL_WEEK_NBR]), 'D_DATES (ALL)'[CAL_DATE] = VALUES('D_DATES (All)'[LOWES LAST QOH DATE])) RETURN IF (MAX('D_DATES (Weekly)'[LOWES_FISCAL_WEEK_NBR] ) = __LCWNumber, "Yellow"))