Tracking Growth on Changing Exchange Data

[lshelton]

Greetings all,

I am pretty new to Power BI, and I was looking for assistance with a solution we developed for a client.  They were looking for a way to track Exchange meeting categories for employee professional development.  Basically, they want visualizations and data about how many of their meetings were categorized in a certain way.  I achieved this by importing data from Exchange including the Category column, and creating a Duration column by subtracting the End from the Start. 

If it matters, I also added two measures, one for a sum of the categorized meetings, and one for that sum to be subtracted from the number of hours in a work week or month, so that they could compare time spent in those categories versus time not spent in those categories.

The client is additionally looking for a way to track this data longitudinally.  Keep in mind, this needs to be a relatively simple solution since we are asking their employees to do it for themselves, but this is a pbix file that they will refresh and re-filter each month, so I'm not sure of the best way to be able to track and show that data.

Including a screenshot of the sample visualizations and the Power Query table, filtered to show just this month.  Please let me know if I need to provide more information - as I said, I'm new to this - and I appreciate any assistance in advance!

[V-lianl-msft · Accepted solution]

Hi @lshelton ,

Based on your description, you need to create a disconnected calendar table as a slicer, and then create a measure to show the differences of the previous months.

Here is a sample .pbix

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

[lshelton]

OMG after some extra tweaking and with your sample file, I finally got what I was looking for!  Thank you so much, I couldn't have done it without you!

[dedelman_clng]

Hi @lshelton - what do you mean by "track this data longitudinally"?

[lshelton]

Being able to look at data from January, February, March etc. even though in general this is a dashboard that is getting updated with just that month or week's data.  They want to be able to see growth or change month by month.

[dedelman_clng]

My suggestion would be (instead of doing all of the work in Power Query) to bring the raw/detail/transactional data all the way into the Power BI model and do the calculations/groupings with measures. Since each "transaction" should have a date on it, you can connect to a calendar table and show values plotted against the month/year.

Hope this helps

David

[lshelton]

Yes, each calendar event has a date attached to it, I was just filtering those dates in Power Query.  I sort of understand what you are talking about, but haven't touched calendar tables.  Would you by any chance have a link to a walkthrough you like for calendar tables to get me pointed in the right direction?  

[dedelman_clng]

Try here: Power BI Time Intelligence or Google/Bing "time intelligence".

David

[lshelton]

Hmm it doesn't look like a date table would work because there are more than one entry per day sometimes and some days don't have any entries.

[dedelman_clng]

Hi @lshelton  - the Date table / Calendar table is designed for exactly that case.  The calendar has all of the dates, and it joins to a key date table on your fact/detail as a Many-to-1 relationship, so it's ok if there are gaps in the detail.  You then use the data from the Date table as your longitudinal axis and it automatically filters the detail.

Take a look at this blog post about calendar tables: Time Intelligence Calendar Table 

David

[lshelton]

Thank you, I will give this a try and see how it goes!  Appreciate the guidance!