Forum Discussion
Calculating Average Occupancy Between Times
Hello All,
I am working on a project in which:
I have occupancy data for rooms where the occupancy data is collected every 5mins
I have meeting data which has a meeting start and end time.
What I would like to do is come up with a formula that allows me to do the following:
Where the room & Date are the same between the tables, Average all of the Average Occupancies between Meeting Start & End Time.
I want this to be dynamic and capable of working on the rest of the data set. The tables above are simply filtered to only 1 room on a given day between a time frame. I want this to be able to calculate the average occupancy for any room on any date within any time frame for each meeting.
Thanks in advance and truely appreciate all the help I can get.
Hi Anonymous
Do you use this formula to create a calculated column in the table1->Meetings?
If so, please modify with the following formula
average = CALCULATE( AVERAGE(Censor[Average Occupancy (%)]), FILTER( ALLEXCEPT(Censor, Censor[Space Name]), [Date] = Meetings[Start Date] && [Date Time] >= EARLIER(Meetings[Start Time]) && [Date Time] <= EARLIER(Meetings[end time]) ) )If it doesn't solve your problem,
could you create a pbix only contains some rows of these two table and apply your formula as the screenshot and then share with me?
Best Reagrds
Maggie
9 Replies
Anonymous
Are you able to post your file or a sample of your data?
I cannot share the data how it currently stands as for it does have private data inside.
However with the 2 pictures this should give enough of what I want the formula to be capable of doing
Essentially the total in the "Occupancy" table needs to be able to relate to the row of data in the "Meeting" table so that I could place a measure in the "Meeting" table that averages all the occupancy rows for a meeting that has specific start date, time and end time.Ok, could you walk me through a numeric example ? Thx
