A date granularity problem...not sure if this is possible??

[Russ Skinner]

Not sure if the following is possible using PowerPivot either with a measure or calculated column, or whether I'll have to manually create a new table with the data - long hand.

Following is a simplified example of what I'm trying to achieve.

Following two tables linked to power pivot -

Energy, which contains energy billing data thus

and a standard calendar table which runs from the start of 2011 to end of 2014.

The tables are linked by date fields.

For each line of billing data I've created a simple measure to calculate average energy use per day. So for first billing period = 700/79 = 8.86 units.

Now for the tricky part. I would like to be able to select a start and finish date or quarter (as defined in calendar table) and calculate the total units based on the billing period averages.

So for example from the 01/01/2012 to 31/12/2012 would use averages from the first 5 rows of the energy table summed over the applicable days of the bill periods between these two dates.

Similarly Quarter 1, 2012 would sum between the start and finish dates of this quarter defined in the calendar table.

Any ideas greatly appreciated.

Thanks

Russ