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

LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

Anonymous

 

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.