Conditional format latest value per user

[deaconb]

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)
)

I want to color a given user's maximum week orange. I thought I could accompish doing this with my measure above and setting 2 to be orange (the field format is number), but it's only formatting the absolute latest training date (e.g., green box value) and not everyone's latest training week date (e.g. red box values). I realize I am currently using gradient conditional formatting but it shouldn't affect that the latest training week for a user isn't shading properly.

Here's some truncated sample data to show you my measure is working properly: 

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

[Sahir_Maharaj]

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
IsLatestWeek

[deaconb]

Unfortunately 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. 

[deaconb]

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.