I hava matrix looking at course attendance at a college and the calculations are all working fine. I have been asked to do some conditional formatting based on the previous value (red is where attendance is lower than the last week of data, orange is the same, green is better than the previous attendance). Because students attend in different patterns across the year, the week commencing dates may not be consecutive:
You can see here that the value for 02/11/2020 is green because that particular course had no register marks in w/c 26/10/2020 (this is different to 0% attendance). The entry for 02/11 on the top line should be red when compared to 19/10, as it is lower than 19/10. The entry for 02/11 for the second line should be orange, as it is the same as 19/10. These gaps in the attendance for these courses is thrown out by the gaps in their attendance. 26/10 has a column because there are values further down the mtrix, of course.
I’ve searched on the forum and have tried to adapt a formula from @Greg but I can’t quite make it work. Additionally, the first week in the year always shows green because it is being regarded as better than some non-existent value, or rather, one I can’t see. I would want that to be #E7D6EA, which is the background colour to my table.
This is what I have at the moment:
Week Commencing Attendance Overall Colour =
VAR SelectedWeek =
SELECTEDVALUE ( Dates[Week Commencing] )
VAR PrevWeek =
MAX ( Dates[Week Commencing] ),
FILTER ( ALL ( Dates ), Dates[Week Commencing] < SelectedWeek
&& NOT(ISBLANK( Dates[Week Commencing])))
VAR SelectedWeekAttendance =
CALCULATE ( [Attendance Overall], Dates[Week Commencing] = SelectedWeek )
VAR PreviousWeekAttendance =
CALCULATE ( [Attendance Overall], Dates[Week Commencing] = PrevWeek )
SelectedWeekAttendance < PreviousWeekAttendance, “#D98880”,
SelectedWeekAttendance = PreviousWeekAttendance, “#FAE5D3”,
SelectedWeekAttendance > PreviousWeekAttendance, “#D4EFDF”,
My Dates table is related to the Attendance table (which carries all the attendance marks) via the Date to Session Date link.
Is anyone able to point me in the right direction? I have a lot of student and staff data in my .pbix, so randomising it or masking data would not be straightforward, so that would really be a last resort. I do appreciate that it would be useful to help solve this, though.