Forum Discussion

ChubbChubb's avatar
ChubbChubb
Icon for Helper I rankHelper I
2 years ago

Attendance type over 10 Weeks

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 wo...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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