Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 %
I'm not sure to be honest, Its a shame I cna't just upload a file to the forum
Hi @ChubbChubb ,
You can upload pbix files to OneDrive, Google Drive and share the links.
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
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
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
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.
Can you please share sample data ?
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
User | Count |
---|---|
85 | |
85 | |
67 | |
61 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |