Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Date table that has the following columns:
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!
Solved! Go to Solution.
Hi @jbenedict2,
See my solution here.
Hope this helps.
Hi,
Share the dataset and also show the exact result that you are expecting.
Hello,
Here is a link to a mock-up of my dataset with the desired result explained:
Mock Up for Utilization Rate Help
Note that the data source type is not the same as in my actual file; I've used Excel to replicate the structure and contents since I cannot share a file with connections to our college systems.
Please let me know if there are any issues opening the file - this is my first time trying to share one on the forum 🙂
Thanks,
Jess
Hi,
If i look at the first row of your meeting info table, does it mean that for CRN 75119, the class will be help on all Thursdays between the date range March 1, 2018 and May 24, 2018 for 2 hours? In that case, the total hours used would be
Number of Thursdays between that date range * 2
Is my understanding correct? Also, how will one compute the total hours available?
Hi,
Yes, that is correct for CRN 75119, and for the total hours used.
The total hours available will actually be a static number (12 hours).
Thanks,
Jess
Hi @jbenedict2,
Share the data in an Excel file format. I need to apply a transformation on the underlying data.
Hi @jbenedict2,
I do not see an option to download the workbook from there. I can just see the data there but there is no download option.
Hi,
If you hover over the file three dots should appear to the right of the title; when you click the dots there should be an option to download the file.
Let me know if that doesn't work and I'll try moving the file to something like DropBox.
Thanks,
Jess
Hi,
There is no download option when i click on the 3 dots.
Hi @jbenedict2,
See my solution here.
Hope this helps.
Hi,
This is perfect! I was able to adapt it a bit so that it works in Power BI (just a matter of adjusting the syntax some).
Thank you for all of your help!
Jess
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |