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

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

    • 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