Calculating Average Occupancy Between Times

[Anonymous]

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.

[LivioLanzo]

@Anonymous

Are you able to post your file or a sample of your data?

[Anonymous]

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.

[LivioLanzo]

Ok, could you walk me through a numeric example ?  Thx

[Anonymous]

Not sure how to better explain this, but here is an attempt.

What needs to be done is I need to write up a measure which will be placed in this table 

Where we can see from the other table it is the same date, same room and all the occupnacy rows are between the start time and end time... 
so for the "new measure column" it would take the total average of all the rows below


So in the first table we would have 29.60 display in the new column.

Basically I think the formula needs to follow this type of pragmatic thinking:
When Room # & Date is the same between both tables
Average all of the Average occupancies 
Where the Date time is between the start time and end time of the meeting table

Hopefully this helps?

[Anonymous]

I've been trying to put something together, but am quite new to DAX.

essentially I think something similar to this would work

If('Meetings'[Room] == 'Censor'[Room] && 'Meetings'[Start Date] == 'Censor[Date] && 'Censor'[Date Time] >= 'Meetings'[Start Time] && 'Censor'[Date Time] <= 'Meetings'[EndTime],
Average('Censor'[Average Occupancy %])
)

This isn't the correct syntax, but think it could help

[v-juanli-msft]

Hi @Anonymous

Create a calculated column as below (sheet5->table1, sheet6->table2)

average = CALCULATE(AVERAGE(Sheet6[av]),FILTER(ALLEXCEPT(Sheet6,Sheet6[room]),
                       [date]=Sheet5[start date]&&[datetime]>=EARLIER(Sheet5[start time])
                              &&[datetime]<=EARLIER(Sheet5[end time])))

Best Regards

Maggie

[Anonymous]

thank you for your help however I do seem to have a problem still in regards to this measure.

average = 
CALCULATE(
    AVERAGE(Censor[Average Occupancy (%)]),
        FILTER(
            ALLEXCEPT(Censor, Censor[Space Name]),
                [Date] = Meetings[Start Date] && [Date Time] >= EARLIER([Start Time]) && [Date Time] <= EARLIER(Meetings[end time])
        )
)

this happens to leave the average column all blank (below is pictures of the tables whited out with anything I can't share
Meetings table (table 1)

table 2

I am honestly not certain as to why this would display all blank

Thank you for your help thus far, highly aprreciated

[v-juanli-msft · Accepted solution]

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

[Anonymous]

The formula was correct it was the modeling of the data that was incorrect. The time feilds came in as date time and I guess this didn't work properly due to that. thanks