Hello. I am trying to calculate the space utilization rate for classrooms on a daily basis. Space utilization rate is defined as (Hours Used/Hours Available)x100. I've been able to figure out how to calculate the Hours per Session (which I plan to then sum by day to get Hours Used), but where I'm stuck is mapping out those Hours per Session to the specific dates that the class takes place. I've gotten as far as using a GENERATE statement using my Meeting Info table and my Date table to get the dates between the start and end dates of the class, but I can't figure out how to get only those dates that reflect the days of the week when the class takes place.
Here's my data structure:
Meeting Info table that has the following columns:
- CRN (5 character string that serves as a class section identifier) [note that there can be multiple rows per CRN due to multiple meeting patterns]
- Start Time
- End Time
- Start Date
- End Date
- Days (shows days of week that class takes place ex. TR for Tuesday and Thursday)
- Location (the room where the class takes place)
- Hours Per Session (calculated column)
Date table that has the following columns:
- Calendar Date
- Day Name
- Day Abbreviated (M = Monday, T = Tuesday, etc. so that the days are the same as those used in Days column of Meeting Info)
- More standard Date table columns (Calendar Month Name, Quarter, etc.)
The GENERATE statement that I've been trying to work with (although I'm open to getting rid of it if necessary) is:
DatesBetweenStartAndEnd =
GENERATE (
'Meeting Info',
FILTER ('Date',
'Date'[Calendar Date] >= EARLIER ( 'Meeting Info'[Start Date] )
&& 'Date'[Calendar Date] <= EARLIER ( 'Meeting Info'[End Date] )))
Would anyone be able to help with either modifying the GENERATE statement to pull only dates that match up with days of the week when a class takes place or with a new method of being able to calculate space utilization rate by day? Thanks in advance!
