Display previous attendance figure

Question

I have a table 'Date' with a column[Attendance] which has a mixture of values and zeros. I am looking for a code for Power BI that would display the previous attendance in a calculated column next to the attendance column so that I can use the information in a visual to show the difference of attendance between the 2 matchdays, unless there is a better way of doing this.

If site has zero in attendance then it was closed, if we could display a zero or "Site Closed" text.

Date	Attendance	Last Games Attendance
06/01/24	0	Site Closed
07/01/24	52314	Site Closed
08/01/24	0	Site Closed
09/01/24	0	Site Closed
10/01/24	16895	52314
11/01/24	0	Site Closed
12/01/24	0	Site Closed
13/01/24	48536	16895
14/01/24	78236	48563
15/01/24	12247	78263
16/01/24	0	Site Closed
17/01/24	52314	12247

I am not sure where to start with this, I can get it to work on excel with a different set of data using an INDEX and MATCH formula but haven't got a high level of experience using Power BI.

I have tried the below, which just populates the row below

PreviousAttendance = 
CALCULATE(
    MAX('Date'[Attendance]),
    FILTER(
        'Date',
        'Date'[Date] = EARLIER('Date'[Date]) - 1
    )
)

When I remove the -1 it fills the correct row but just with the same figure that is in the attendance row not the previous attendance figure.

Result from Andrii's suggestion, just about there.

Date          Attendance             Last Games Attendance
06/01/24          0                  Site Closed
07/01/24      52314                  Site Closed
08/01/24          0                  Site Closed
09/01/24          0                  Site Closed
10/01/24      16895                  52314
11/01/24          0                  Site Closed
12/01/24          0                  Site Closed
13/01/24       48536                 16895 = Result from code 52314 
14/01/24       78236                 48563
15/01/24       12247                 78263 = Result from code 48563
16/01/24          0                  Site Closed
17/01/24       52314                 12247

Answer 1

Try the following:

Last Game Attendance Number = 
VAR CurrentDate = 'Date'[Date]
VAR LastAttendanceDate = 
    MAXX(
        FILTER(
            'Date',
            'Date'[Date] < CurrentDate && 'Date'[Attendance] > 0
        ),
        'Date'[Date]
    )
RETURN
    IF(
        'Date'[Attendance] = 0,
        BLANK(),
        LOOKUPVALUE('Date'[Attendance], 'Date'[Date], LastAttendanceDate)
    )

after that you can create another column that would produce the label:

Last Games Attendance Label = 
IF('Date'[Last Game Attendance Number] = BLANK(), "Site Closed", FORMAT('Date'[Last Game Attendance Number], "0"))