Create a Planning View of Your Outlook Diary in Power BI

I was chatting to my wife the other day discussing all the things we want to do next year. It has long been a pain in the butt to get a high level (big picture) view of your Outlook diary to see what is happening (think forest, not trees).  In the past I have used some VBA in Outlook that produced an HTML year view (it was pretty good actually), and you can also create your own views in Outlook directly.  But I decided to use Power BI to try to solve this problem.

Load Data from Exchange

The first thing I did was to connect Power BI Desktop to my Exchange server.  There are 2 choices and it depends on your setup which you use.  I have my own domain and O365 configured to use Microsoft Exchange, so I connected to the second one below.

image

The Mailbox address is simply my email address as shown below.

 

image

For this scenario, I only connected to the Calendar, but you can connect to a number of other data objects too as seen below (that may be another blog one day).

image

I then edited the query to just keep the data I wanted.  I used “choose columns” to select just the columns I needed for my report (this is good practice).  In my case I only decided to load the “All Day” events as these are the “big picture” things I wanted to see in the year ahead.

image

If you do this yourself, you may choose to load Categories and use those to determine what to load, but in my case I am only loading all day events.

I then filtered to keep just the “All Day” events, then removed the IsAllDayEvent column.

image

Next I created a Duration column (Add Column\Custom Column)

image

And then I changed the column type to Integer.

image

And finally loaded the data.

Visualising the Big Picture

I looked into a few different visuals to visualise this data, and actually I couldn’t find one that did exactly what I wanted.  I looked at several Gantt Chart visuals and also most (if not all) of the calendar visuals.  The one I found closest to my needs was the Calendar by MAQ Software.  You can see it configured for this data below.

image

This visual allows a monthly view of the big picture.  I would actually prefer a scrolling full year view – I sent an email to MAQ software requesting this enhancement.  Maybe you can send an email too to help get that prioritised.  Just click here.

A Matrix to View the Data

Given the above month view was not quite what I was after, I created another view using a Matrix as shown below.  My idea is to have a single row per week with bars indicating when I have something happening (All Day events of course).

image

There are a few tricks in this one.  This is what I did.

    • I created a calendar table and added the following columns
      • Day Name
      • Day Number of week (starting from Sunday) for sorting purposes
      • Start Date of week.
    • I did not join the calendar table to the data table, but instead I wrote a measure that picks up the filter from the Matrix and pushes it onto the data table.  I needed to do it this way as there are 2 date columns in the events table.  Here is my measure.
Count of Events = VAR CurrDate = MAX('Calendar'[Date])
     VAR FilteredDiary = FILTER(Diary,Diary[Start] <= CurrDate
          && Diary[End] >=CurrDate)
     VAR TotalEvents = COUNTROWS(FilteredDiary)
     return TotalEvents
    • Added a Matrix as shown above.
    • Added the Count of Events measure to the Matrix
    • Applied conditional formatting to the matrix (shown as 1 above)
      • Background colours the same for Min and Max
      • Do not apply formatting for blanks
      • Font colour the same as the background colour
      • The net result is the bar chart above.
    • I then created a Tool Tip (2 above) and configured it for the matrix.  I had to create a special measure (because there is no relationship).
Name of Events = VAR CurrDate = MAX('Calendar'[Date])
     VAR FilteredDiary = FILTER(Diary,Diary[Start] <= CurrDate
         && Diary[End] >=CurrDate)
     VAR Events = CONCATENATEX(FilteredDiary,Diary[Subject],", ")
     return Events

Publish to PowerBI.com and access via Mobile

Once you have this template up and running, you could publish it to PowerBI.com and then access it from the mobile app where ever you are.

Why not Build this Yourself, or Buy it as a Template

I provide a lot of learning material for free on my site, including this information above.  I love doing this but unfortunately it doesn’t pay the bills.  I decided to offer a working version of the Outlook Annual Planning View as a Power BI template if you would like to purchase it (single user) and save the effort of building it yourself.  The price is US $2.95 (AU $3.95) and you can purchase it for immediate download from my shop.

I will provide support to get it working for you in case there are issues, and depending on how many I sell I may enhance it in the future based on feedback from the community.

The prerequisites for this template to work:

  1. You must be using Exchange Server or Exchange Server Online
  2. It will only display All Day events from your calendar (although you can change that if you want).

What Other Templates do you Need?

Let me know in the comments below if there are other templates that you need to do your job.  Depending on demand and complexity, I may build some more of these in the future.

Share?

Comments

  1. Nice idea! But when tring to connect to my personal exchange account, I get the “Autodiscover service not runing” error. And after a few quick looks on the “resolutions” internet gives…. I do not want to spend a long time fixing this. (so yet another annoying hour, just trying to connect to data….for the 345 th time)

  2. Mmm, that is not my area of expertise unfortunately. Did you try both of the connector types (exchange and exchange online)?

  3. I look for something similar, but with multiple (source) calendars from my entire team. Any idea if this would work?

  4. This is a great idea, i had not thought of linking to Excel. i have a vision: show a list of how many days each of my emails has sat in either the inbox or todo folders or whatever. i use my inbox as a todo storage, not good i know, but some items get away from me in terms of time. an aged listing of items greater than X days.

Leave a Reply

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x