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

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.

LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

      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'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