Over the last few months, we’ve been exploring Time Phased reporting using Excel.  In our last post on this subject, Project Online Time Phased Reporting in Excel part II, we talked about using the Time table in Odata reporting. This handy table gives you several date references for a huge list of dates so we can report on them.

One piece of date information is the week number.  So, if a week started on 1/1/2015, the week number would be 1. If a week started on June 25, the week number would be 26.  Our users often want to see data in weekly increments, and we were concerned this might create confusion.

So, once again, we pulled out our handy PowerPivot calculated fields functionality.

We created a new field at the end of the Time table, which we called Week Start. We used this formula:

=[TimeByDay]-WEEKDAY([TimeByDay],1)+1

This gave us a week our users could relate to:

image

For more tips, explore other PowerPivot blog posts on our site:

Project Online Reporting with Excel- Why Isn’t My Data Model Working-

Project Online–Time Phased Reporting in Excel, part II

Project Online – Time Phased Reporting in Excel

Tips for Using Excel Web Services for Project Server BI

Fixing Powerpivot Table Relationships for Odata

How to Edit a Connection String in Excel PowerPivot