It is interesting how we have different ways of tackling things in Power Bi - particularly around attendance which brings particular challenges including terms, academic year start date etc.... One of the challenges I face is that I have one export of attendance data from SIMS and then Power Bi reports for different year groups. We ask our PLT to do analysis of their data, so not only do I want YTD and termly attendance but I also want attendance data up to a cut-off reporting date in each term, which is not the same for each year group (LOL)
Have an Academic Data Table has really helped me with my attendance data and also calculating attendance for selected periods.... This is a bit long winded but some may find it helpful.
PART 1
I have a "lookup table" with term names and start / end dates - called Lookup_Term (imaginative naming I know!). This also has my report cut off date, so that I can "fix" data to the end of the reporting period even when I have attendance data after that date. This table is created in Power Query from scratch.
PART 2
I have a calculated table in Desktop (which I mark as a date table) which works out which term each date belongs to, what academic (as opposed to calendar) week, day names etc:
Academic Date Table = CALENDAR(MIN(Lookup_Term[TermStartDate]), today() )
This table then has calculated columns as follows:
Academic Year =
// Credit to PowerBiForSchools for this DAX formula
VAR ThisYear =
YEAR ( 'Academic Date Table'[Date] )
VAR LastYear = ThisYear - 1
RETURN
IF (
MONTH ( 'Academic Date Table'[Date] ) > 8,
//if the month is greater than 8 (August)
ThisYear & "/" & ThisYear - 1999,
//else if the month is NOT greater than 8
LastYear & "/" & ThisYear - 2000
)
----
Year = YEAR('Academic Date Table'[Date])
------
Day = format(WEEKDAY('Academic Date Table'[Date]),"ddd")
----
Month = 'Academic Date Table'[Date].[Month]
---
Term =
CALCULATE(
MAX(Lookup_Term[Term]),
FILTER(lookup_term,
Lookup_Term[TermStartDate] <= 'Academic Date Table'[Date] &&
Lookup_Term[TermEndDate]>= 'Academic Date Table'[Date])
)
-----
CalWeek = WEEKNUM('Academic Date Table'[Date])
---
The next column works out "academic week" starting from 1st september
AcWeek =
VAR __aw = [Calweek] - 36 + 1
RETURN IF(__aw<=0, 52+__aw,__aw)
-----
DayNo = WEEKDAY('Academic Date Table'[Date])
(In order to display the days in the correct order in visuals, highlight the Day column and sort by DayNo column)
PART 3
Each report page has a slicer based on the terms in the Academic Date Table.
Users can select a term to display data for that term - in the case of attendance it is from start of year to report end date (but could easily be term end date). I have a measure that picks up the term selected from the slicer:
SelectedRepTerm = SELECTEDVALUE(Academic[Term],blank())
Then two more measures to work out start and end dates that can then be used for calculating attendance. My attendance data is in a table called AttendanceYTD.
SelectedTermEndDate = lookupvalue(Lookup_Term[Report Ends],Lookup_Term[Term],AttendYTD[selectedRepTerm])
SelectedTermStartDate = lookupvalue(Lookup_Term[TermStartDate],Lookup_Term[Term],AttendYTD[selectedRepTerm])
I also have one more measure that picks up the report "cut off" date for that term:
SelectedTermEndDate = lookupvalue(Lookup_Term[Report Ends],Lookup_Term[Term],AttendYTD[selectedRepTerm])
You can then count absent and present (using statistical meaning as pointed out above)
Absent/Present Counts would look something like this - CountPresent = sumx(AttendYTD,if(AttendYTD[statistical meaning]="Present",1,0))
These can then be used to calculate %YTD, % Term etc
Individual term attendance:
Term%Present = calculate([CountPresent]/([CountAbsent]+[CountPresent]),filter(AttendYTD,AttendYTD[Mark date]>=AttendYTD[selectedTermStartDate] && AttendYTD[Mark date]<=AttendYTD[selectedTermEndDate]))
Hope that helps!!!!
