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

      • 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