Use Power BI to create a dynamic/live meeting room schedule

I recently set out to create a “live” conference room schedule that could be presented constantly on an auto-refreshed screen outside conference rooms. This would replace printed schedules placed in holders outside the rooms. The following example uses a SharePoint calendar as the conference room calendar and can be refreshed constantly using Power BI’s scheduled refresh in O365 or Report Server.

If you’re using Power BI Report Server in an on-premises environment, you can refresh your data every few minutes. If you’re O365 you’ll need to check your license for the max number of times you can refresh per day.

Also note that Microsoft Exchange as a data source cannot be automatically refreshed so you’ll need to explore PowerShell, Azure data streaming or another solution to use with Exchange to get “live” data. You could also just take out the “minutes” granularity of this solution and refresh manually daily. This might not be an issue if your calendar doesn’t change much throughout the day.

Preparation

• Get Power BI
  • On-premises: Power BI Report Server
  • O365/Other: Power BI Desktop, best-installed from the Microsoft Store for auto-updates but also available for download here.
• Create a new Power BI report with a calendar data source loaded (get data –> select your source). We’ll only use these columns:
  • Title/Subject (Text)
  • Start (Date/Time)
  • End (Date/Time)
  • Location (Text, optional)
• Make sure dates are formatted in Power BI as date/time
  • Click column under “Fields”
  • In Modeling Tab, make sure Data type is Date/Time
    

Add columns

Create the following calculated columns (Modeling –> New Column):

Note: Once created you can hide (right-click column name –> hide) GREEN columns since they’re only used in other columns. Also be sure to replace ‘Calendar'[Start/End] with the name of your own data table and column names.

Duration = if(DATEDIFF('Calendar'[Start],'Calendar'[End],HOUR)<1,DATEDIFF('Calendar'[Start],'Calendar'[End],MINUTE),DATEDIFF('Calendar'[Start],'Calendar'[End],HOUR))

Duration Label=if(DATEDIFF('Calendar'[Start],'Calendar'[End],HOUR)<1,CONCATENATE('Calendar'[Duration]," mins"),if(DATEDIFF('Calendar'[Start],'Calendar'[End],HOUR)=1,CONCATENATE('Calendar'[Duration]," hour"),CONCATENATE('Calendar'[Duration]," hours")))

EndTime = TIMEVALUE('Calendar'[End])

TimeUntilEnd = CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE("Ends at ",'Calendar'[EndTime])," (in "),MINUTE(('Calendar'[End])-(NOW())))," minutes)")

HappeningNow = if('Calendar'[Start]<NOW(),if('Calendar'[End]>NOW(),1,0),0)

What's Happening = if(CALCULATE(SUM('Calendar'[HappeningNow]),ALLSELECTED('Calendar'))=0,"See upcoming events below.",CALCULATE(MAX('Calendar'[TimeUntilEnd]),'Calendar'[HappeningNow]=1))

Happening Now Text = if('Calendar'[HappeningNow]=0,"Vacant",'Calendar'[Subject])

Future = if('Calendar'[End]>now(),1,0)

Add filters

1. If you have location as a column, you can do a separate page/report for each location. Drag “location” to “Page level filters” and check the box of the room you’re wanting to show.
   
2. Add your “End” and “Future” columns to “Report level filters”. Set “End” to “Relative date”, next 30 days and “Future” to “1” so that events included in “Today” but already passed don’t show up.
   

Build the report

If you’d like your report to resemble mine, here is the gist of my setup:

1. With nothing selected (or click white space on report), click the paint roller icon to format the report and change page size to 4:3
   
2. Add logos and text as you wish to the top
3. Add the “Card” visual for the happening now block
   1. Add value “Happening Now Text”
   2. Data label
      1. Size 27, Segoe (Bold)
   3. Category label OFF
   4. Word wrap ON
   5. Title ON
      1. Happening Now:
      2. White font, dark background, left aligned
      3. Size 25, Segoe UI Light
   6. Resize to fit 100% of the width
4. Add another “Card” visual for the “See upcoming events below” dynamic text.
   1. Add value “What’s Happening”
   2. Data label
      1. Size 18, Segoe UI Light
   3. Category label OFF
   4. Word wrap ON
   5. Title OFF
   6. Resize to fit 100% of the width
5. Add a “Table” visual for up next
   1. Add values “Title/Subject”, “Start” and “DurationText” renamed as you wish
   2. Table style minimal
   3. Grid settings as you wish. I would suggest increasing row padding at least
   4. Column headers
      1. Dark, Segoe (Bold), Size 14
      2. Auto-size OFF
   5. Values
      1. Dark fonts, light backgrounds
      2. Size 16, Segoe UI Light
   6. Title ON
      1. Up Next:
      2. White font, dark background, left aligned
      3. Size 25, Segoe UI Light
   7. Click the “Start” header to sort by earliest to latest
6. If you want the current date/time included, just add a new measure called “Now” with formula as Now=Now() and place that in a Card visual formatting font size and placement as you wish.

Oh heck, here’s the template. Be sure to add your own data source and fix the connections.

The refresh

Save your report to Report Server or publish to O365/Power BI Service.

In Power BI Report Server, find the report in its folder and:

1. Click the ellipses
2. Manage
3. Data Sources (and sign in with account with appropriate credentials)
4. Scheduled refresh and set the schedule

In O365/Power BI Service:

1. Under Datasets on the left, click the ellipses and Schedule Refresh
2. Expand and make sure Data source credentials are the ones you want to use, then expand scheduled refresh, set the schedule and times you’d like and click “Apply”
   

Good luck!