Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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. ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
    Anonymous
    Not applicable
    7 years ago

    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