Attendance type over 10 Weeks

[ChubbChubb]

I need to work out on a rolling 10 week block how many particular attendance codes students have recieved during that 10 week cycle and if it is 10 or more flag them.

This is the code I use to work out the attendance type %

Unauthorised = CALCULATE(

        COUNTROWS(ROLL_CALL_ATTENDANCE),

            FILTER(ROLL_CALL_ATTENDANCE,ROLL_CALL_ATTENDANCE[IS_UNAUTHORIZED_ABSENT]=TRUE()),

            FILTER(ROLL_CALL_ATTENDANCE,ROLL_CALL_ATTENDANCE[IS_POSSIBLE_ATTENDANCE]=TRUE())

          )

My thoughts are to do a formula that looks up todays date and gets the WEEK INDEX from the date table and then work back 9 weeks to get a start date add it as a filter, and then if it counts more than 10 in that time flag them True/False

not sure if I am on the right track with that?

All help appreciated.

[v-zhouwen-msft]

Hi @mh2587 ,thanks for the quick reply.

Hi @ChubbChubb ,

I can't seem to open the link you shared.

[ChubbChubb]

@v-zhouwen-msft it should work now to download the file.

[v-zhouwen-msft]

Hi @ChubbChubb ,

Are there other ways you can share your files?

[ChubbChubb]

I'm not sure to be honest, Its a shame I cna't just upload a file to the forum

[v-zhouwen-msft]

Hi @ChubbChubb ,

You can upload pbix files to OneDrive, Google Drive and share the links.

[ChubbChubb]

that's what I did with the link here it is again example data.pbix

[v-zhouwen-msft]

Hi @ChubbChubb ,

Please follow these steps:

1. Use the following DAX expression to create a column in 'DATE_TABLE'(If today is not within a semester, find the nearest future date within a semester)

Column = IF(ISBLANK([Term Week Index]),MINX(FILTER('DATE_TABLE',[Date] >= EARLIER([Date]) && [Term Week Index] <> BLANK()),[Date]))

2.Use the following DAX expression to create a table named 'Table'

Table = FILTER('DATE_TABLE','DATE_TABLE'[Term Week Index] <> BLANK())

3.Use the following DAX expression to create a column in 'Table'

Column 2 = COUNTROWS(FILTER('Table',[Date] <= EARLIER('Table'[Date])))

4.Use the following DAX expression to create a measure(I assumed the date.)

Measure = 
VAR _today = DATE(2021,12,16)
VAR isTearmDay = LOOKUPVALUE('DATE_TABLE'[Term Week Index],DATE_TABLE[Date],_today)
VAR _a = LOOKUPVALUE(DATE_TABLE[Column],'DATE_TABLE'[Date],_today)
VAR startDay = IF(ISBLANK(isTearmDay),_a,_today)
VAR _b = LOOKUPVALUE('Table'[Column 2],'Table'[Date],startDay) + 69
VAR endDay = LOOKUPVALUE('Table'[Date],'Table'[Column 2],_b)
VAR _c = SELECTCOLUMNS(FILTER('Table','Table'[Date] >= startDay && 'Table'[Date] <= endDay),"Date",[Date])

VAR _d = CALCULATE(
        COUNTROWS(ROLL_CALL_ATTENDANCE),
            FILTER(ROLL_CALL_ATTENDANCE,ROLL_CALL_ATTENDANCE[IS_UNAUTHORIZED_ABSENT]=TRUE()),
            FILTER(ROLL_CALL_ATTENDANCE,ROLL_CALL_ATTENDANCE[IS_POSSIBLE_ATTENDANCE]=TRUE()),
            FILTER(ROLL_CALL_ATTENDANCE,ROLL_CALL_ATTENDANCE[DATE] IN _c)
          )

RETURN _d

[ChubbChubb]

example data.pbix

[mh2587]

Rolling_10Week_Attendance_Flag = //Try this one
VAR StartDate = TODAY() - 63  // 63 days = 9 weeks
VAR EndDate = TODAY() // Current date
RETURN
    CALCULATE(
        COUNTROWS(ROLL_CALL_ATTENDANCE),
        FILTER(
            ROLL_CALL_ATTENDANCE,
            ROLL_CALL_ATTENDANCE[AttendanceDate] >= StartDate &&
            ROLL_CALL_ATTENDANCE[AttendanceDate] <= EndDate
        )
    ) >= 10

[ChubbChubb]

thanks, I have to take into account school holidays, which is where the WEEK INDEX comes in as my date table contains all dates and I'm using the WEEK INDEX to show which are term dates.

[mh2587]

Can you please share sample data ?

[ChubbChubb]

example data.pbix