Meeting Room utilization by day, week, month, year, etc.

[lamysroe]

I have been trial and erroring and researching for several weeks on how to get the utilization rate of conference rooms based on a calendar. I have a table with the conference room information, a utilization table where I broke down information more, a calendar table which includes working hours per day, and a holiday calendar table in which I want to exclude from working days. 

I can get utilization by year and for days when the room is booked, but I am having issues getting true utilization for days, weeks, month... regardless of whether the room was booked or not. I have provided only snipets of the tables as there are over 30 conference rooms. 

Conference Rooms

Room

Office

Start

End

Video Conference?

End Time

Start Time

Duration

Year

Month Name

Day Name.1

Month

Day of Week

Available Work Hours

Week of Year

Day of Year

A

Office 1

3/21/2017 9:00

3/21/2017 9:30

No

9:30:00 AM

9:00:00 AM

0.5

2017

March

Tuesday

3

3

10

12

80

B

Office 2

3/23/2017 7:00

3/23/2017 17:00

No

5:00:00 PM

7:00:00 AM

10

2017

March

Thursday

3

5

10

12

82

B

Office 2

3/27/2017 14:00

3/27/2017 14:30

No

2:30:00 PM

2:00:00 PM

0.5

2017

March

Monday

3

2

10

13

86

B

Office 3

3/28/2017 14:00

3/28/2017 14:30

No

2:30:00 PM

2:00:00 PM

0.5

2017

March

Tuesday

3

3

10

13

87

A

Office 1

4/4/2017 11:30

4/4/2017 12:00

No

12:00:00 PM

11:30:00 AM

0.5

2017

April

Tuesday

4

3

10

14

94

B

Office 1

4/5/2017 8:00

4/5/2017 8:30

No

8:30:00 AM

8:00:00 AM

0.5

2017

April

Wednesday

4

4

10

14

95

Utilization Table 

Office	Start	End	Room	Video Conference?	End Time	Start Time	Duration	Worktime per Day	Percentage Utilization	Year
Office 1	3/21/2017 9:00	3/21/2017 9:30	A	No	9:30:00 AM	9:00:00 AM	0.5	10	5.00%	2017
Office 2	3/23/2017 7:00	3/23/2017 17:00	B	No	5:00:00 PM	7:00:00 AM	10	10	100.00%	2017
Office 2	3/27/2017 14:00	3/27/2017 14:30	B	No	2:30:00 PM	2:00:00 PM	0.5	10	5.00%	2017
Office 3	3/28/2017 14:00	3/28/2017 14:30	B	No	2:30:00 PM	2:00:00 PM	0.5	10	5.00%	2017
Office 1	4/4/2017 11:30	4/4/2017 12:00	A	No	12:00:00 PM	11:30:00 AM	0.5	10	5.00%	2017
Office 1	4/5/2017 8:00	4/5/2017 8:30	B	No	8:30:00 AM	8:00:00 AM	0.5	10	5.00%	2017

Holidays 

20170101	New Years
20170529	Memorial Day
20170704	Independence Day
20170904	Labor Day
20171123	Thanksgiving
20171124	Friday after Thanksgiving
20171225	Christmas
20180101	New Years
20180528	Memorial Day
20180704	Independence Day
20180903	Labor Day
20181122	Thanksgiving
20181123	Friday after Thanksgiving
20181225	Christmas
20190101	New Years
20190527	Memorial Day
20190704	Independence Day
20190902	Labor Day
20191128	Thanksgiving
20191129	Friday after Thanksgiving
20191225	Christmas
20200101	New Years
20200525	Memorial Day
20200704	Independence Day
20200907	Labor Day
20201126	Thanksgiving
20201127	Friday after Thanksgiving
20201225	Christmas

Calendar

Any thoughts or suggestions?

[Icey · Accepted solution]

Hi @lamysroe ,

I find ‘Conference Rooms’ is similar to ‘Utilization Table’. So, I just use ‘Conference Rooms’ table. This is my PBIX file.

You can create columns and measures like so:

1. Create relationship among three tables.

2. Create columns in ‘Calendar’ table.

Is Holiday =
IF (
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        ALLEXCEPT ( 'Calendar', 'Calendar'[Date] )
    )
        = CALCULATE (
            MAX ( Holidays[Holiday Date] ),
            ALLEXCEPT ( Holidays, Holidays[Holiday Date] )
        ),
    1,
    0
)

Work Hours Actual =
IF (
    'Calendar'[Work Hours] = 0
        || CALCULATE (
            MAX ( 'Calendar'[Is Holiday] ),
            ALLEXCEPT ( 'Calendar', 'Calendar'[Date] )
        ) = 1,
    0,
    10
)

3. Create measures.

utilization =
DIVIDE (
    SUM ( 'Conference Rooms'[Duration] ),
    SUM ( 'Calendar'[Work Hours Actual] ),
    0
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lamysroe]

@Icey Thanks this was very helpful. I was able to get the many to one relationship working as well after playing with it for a bit. 

[amitchandak]

One of the easy ways to create a table having all resources per day using a cross join. And use that as the base for utilization

https://docs.microsoft.com/en-us/dax/crossjoin-function-dax

Other is a working day measure using the slicer date. Use that Var in formula. 

Var Working days= Max(date[date]) - Min(date[date])  - put holiday logic

Use this in your formula.