Matrix Conditional Formating based on previous row

[Gully3583]

Hi everyone,

Need a little help to format a matrix I'm building.

I have two tables:

• Routes: contains the theoretical routes of a bus line with its stops and the order of service
• Timetable: contains the actual times of passage at each bus stop

I have created a key to link these two tables: Key = [IdLine]:[IdRoute]:[IdStop]

I have then created the following matrix in order to show differences between theoretical and actual stop sequence for a route. In order to do so I have displayed theoretical stop sequences on the left side and display actual passage time as value. To make it easier to read, I would like to put in red the cells for which the passage time is greater than that of the previous line (which means that there is a difference between theoretical and actual stop sequence).

Example down below :

I would really appreciate any help, thanks in advance !!

[Gully3583 · Accepted solution]

Thanks for the answer,

I have found a solution thnaks to the OFFSET function. I post it here in case someone is interested 

1. Create a new table

Test = SUMMARIZE(timetable, timetable[LineId], timetable[Route], timetable[Trips], routes[StopSequence], timetable[StopId], timetable[ExpectedTimetable])

2. Create new measure and use it to apply conditional formating on timetable[ExpectedTimetable]

_offset = 
VAR nextrow = 
CALCULATE(
    MAX(Test[ExpectedTimetable]),
    OFFSET(
        1, 
        ALLSELECTED(Test), 
        ORDERBY(Test[Trips], ASC, Test[StopSequence], ASC)
        )
    )

VAR currentrow = 
CALCULATE(
    MAX(Test[ExpectedTimetable]),
    OFFSET(
        0, 
        ALLSELECTED(Test), 
        ORDERBY(Test[Trips], ASC, Test[StopSequence], ASC)
        )
    )
RETURN
    IF(nextrow <> BLANK() && nextrow < currentrow, "Red", "Black")

[Anonymous]

Hi @Gully3583,

Did you mean to find the records which bigger than new one? If that is the case, you can use current ‘stop Sequence’ and category group values to find out current next records and return color code to use as the condition formatting expressions.

formula =
VAR currSequence =
    CALCULATE (
        MAX ( Table[StopSequence] ),
        ALLSELECTED ( Table ),
        VALUES ( Table[LineID] ),
        VALUES ( Table[Route] ),
        VALUES ( Table[Trips] )
    )
VAR currDate =
    MAX ( Table[ExceptedTime] )
VAR nextDate =
    CALCULATE (
        MAX ( Table[ExceptedTime] ),
        FILTER ( ALLSELECTED ( Table ), Table[StopSequence] = currSequence + 1 ),
        VALUES ( Table[LineID] ),
        VALUES ( Table[Route] ),
        VALUES ( Table[Trips] )
    )
RETURN
    IF ( nextDate <> BLANK () && currDate > nextDate, "Red", "Black" )

Regards,

Xiaoxin Sheng