Forum Discussion
Conditional format latest value per user
I'm working on a report which pulls together several sources of training data. One of the widgets we need is for new employees working through the training program and capturing their training events by week. The ask from stakeholders is to color code a given user's lastest training week which I felt I could accomplish from conditional formatting.
I have the following formulas:
1. Week of Training
Week of Training =
var saturdaybefore =
CALCULATE
(MIN('Employee'[Proficiency Training Start Date]) - WEEKDAY(MIN('Employee'[Proficiency Training Start Date]), 2))-1
RETURN
IF(YEAR(saturdaybefore) < YEAR(MIN('Employee'[Proficiency Training Start Date])), BLANK(), ROUNDUP(DIVIDE(DATEDIFF(saturdaybefore, 'Data'[Date of Training], Day),7), 0))
2. I then created a measure to assess the max week of each user:
Measure =
var maxweek =
CALCULATE(
MAXX('Data',[Week of Training] ),
ALLEXCEPT(Data', 'Data'[USERNAME])
)
RETURN
IF(ISBLANK(maxweek), 0,
IF(Max('Data'[Week of Training]) =maxweek, 2, 1)
)
| USERNAME | Date of Training | SYSID | Proficiency Start Date | Week of Training | Measure |
| user1 | 11/6/2024 00:00 | 8609789 | 0 | ||
| user2 | 1/21/2025 00:00 | 9043094 | 10/7/2024 00:00 | 16 | 2 |
| user2 | 12/15/2024 00:00 | 8873091 | 10/7/2024 00:00 | 11 | 1 |
| user2 | 12/11/2024 00:00 | 8850769 | 10/7/2024 00:00 | 10 | 1 |
| user2 | 12/11/2024 00:00 | 8850741 | 10/7/2024 00:00 | 10 | 1 |
| user2 | 12/2/2024 00:00 | 8786133 | 10/7/2024 00:00 | 9 | 1 |
| user2 | 12/1/2024 00:00 | 8777767 | 10/7/2024 00:00 | 9 | 1 |
| user2 | 12/1/2024 00:00 | 8777763 | 10/7/2024 00:00 | 9 | 1 |
| user2 | 11/30/2024 00:00 | 8775141 | 10/7/2024 00:00 | 8 | 1 |
| user2 | 11/30/2024 00:00 | 8775133 | 10/7/2024 00:00 | 8 | 1 |
| user2 | 11/27/2024 00:00 | 8760819 | 10/7/2024 00:00 | 8 | 1 |
| user2 | 11/17/2024 00:00 | 8684304 | 10/7/2024 00:00 | 7 | 1 |
| user2 | 11/13/2024 00:00 | 8659422 | 10/7/2024 00:00 | 6 | 1 |
| user2 | 11/4/2024 00:00 | 8592577 | 10/7/2024 00:00 | 5 | 1 |
| user2 | 11/3/2024 00:00 | 8584769 | 10/7/2024 00:00 | 5 | 1 |
| user2 | 10/30/2024 00:00 | 8565134 | 10/7/2024 00:00 | 4 | 1 |
| user2 | 10/30/2024 00:00 | 8565131 | 10/7/2024 00:00 | 4 | 1 |
| user2 | 10/30/2024 00:00 | 8565127 | 10/7/2024 00:00 | 4 | 1 |
| user2 | 10/24/2024 00:00 | 8526851 | 10/7/2024 00:00 | 3 | 1 |
| user2 | 10/22/2024 00:00 | 8505041 | 10/7/2024 00:00 | 3 | 1 |
| user2 | 10/22/2024 00:00 | 8505026 | 10/7/2024 00:00 | 3 | 1 |
| user2 | 10/16/2024 00:00 | 8467188 | 10/7/2024 00:00 | 2 | 1 |
| user2 | 10/16/2024 00:00 | 8467188 | 10/7/2024 00:00 | 2 | 1 |
| user2 | 10/16/2024 00:00 | 8467188 | 10/7/2024 00:00 | 2 | 1 |
| user2 | 10/11/2024 00:00 | 8439690 | 10/7/2024 00:00 | 1 | 1 |
| user2 | 10/11/2024 00:00 | 8439690 | 10/7/2024 00:00 | 1 | 1 |
| user3 | 12/14/2024 00:00 | 8870221 | 10/7/2024 00:00 | 10 | 2 |
| user3 | 12/8/2024 00:00 | 8827785 | 10/7/2024 00:00 | 10 | 2 |
| user3 | 12/5/2024 00:00 | 8807877 | 10/7/2024 00:00 | 9 | 1 |
| user3 | 12/4/2024 00:00 | 8807664 | 10/7/2024 00:00 | 9 | 1 |
| user3 | 12/4/2024 00:00 | 8806990 | 10/7/2024 00:00 | 9 | 1 |
| user3 | 11/26/2024 00:00 | 8744739 | 10/7/2024 00:00 | 8 | 1 |
| user3 | 11/25/2024 00:00 | 8737160 | 10/7/2024 00:00 | 8 | 1 |
| user3 | 11/24/2024 00:00 | 8732020 | 10/7/2024 00:00 | 8 | 1 |
| user3 | 11/23/2024 00:00 | 8731822 | 10/7/2024 00:00 | 7 | 1 |
| user3 | 11/20/2024 00:00 | 8710436 | 10/7/2024 00:00 | 7 | 1 |
| user3 | 11/10/2024 00:00 | 8633342 | 10/7/2024 00:00 | 6 | 1 |
| user3 | 11/10/2024 00:00 | 8633326 | 10/7/2024 00:00 | 6 | 1 |
| user3 | 11/9/2024 00:00 | 8630349 | 10/7/2024 00:00 | 5 | 1 |
| user3 | 11/6/2024 00:00 | 8603308 | 10/7/2024 00:00 | 5 | 1 |
| user3 | 11/1/2024 00:00 | 8578294 | 10/7/2024 00:00 | 4 | 1 |
| user3 | 10/23/2024 00:00 | 8517792 | 10/7/2024 00:00 | 3 | 1 |
| user3 | 10/22/2024 00:00 | 8509909 | 10/7/2024 00:00 | 3 | 1 |
| user3 | 10/19/2024 00:00 | 8488369 | 10/7/2024 00:00 | 2 | 1 |
| user3 | 10/19/2024 00:00 | 8488358 | 10/7/2024 00:00 | 2 | 1 |
| user3 | 10/9/2024 00:00 | 8425942 | 10/7/2024 00:00 | 1 | 1 |
3 Replies
Hello deaconb,
Can you please try this approach:
Latest Training Week = VAR MaxWeekPerUser = CALCULATE( MAX('Data'[Week of Training]), ALLEXCEPT('Data', 'Data'[USERNAME]) ) VAR IsLatestWeek = IF( MAX('Data'[Week of Training]) = MaxWeekPerUser, 2, -- Indicates the latest week 1 -- Indicates all other weeks ) RETURN IsLatestWeekUnfortunately this results in the same outcome for formatting as above where the expected result is the red boxes and green box being orange, but only the green box is orange.
Your measure and my original measure are basically the same. My measure is already working for assessing the latest week or not. I don't understand why it isn't being accepted as part of the conditional formatting.
