Hi Dani

I will walk you through this but this is the result

I have attached a T10.1 workbook

This is the formula for the difference in months >>DATEDIFF('month',[Start Date],[End Date])+1

This is the calculation for the average >>sum([Cost])/sum([month difference])

Let me know if this helped

Jim

Hi Jim,

I have been following this particular question since beginning and I used another approach as seen in attached. I am stuck towards the end in building some kind of logic to arrive at what he wants.  I am now giving up on this. Pl have a look at the Screenshot and attached if you can build it up from here to help him.

Thanks

Deepak

Thanks for looking at this -

I spent some time looking at your work and also trying a couple of different approaches.

I finally concluded that to get the view he wants we would would need a single record in the data base to act like 2, 3, 4 records depending on the date range in the record. (if that makes sense).  I don't know any way to do that without restructuring the data source.  I looked at scaffolding but that doesn't seem appropriate.

Maybe someone else would know.

Jim

Hi Dani,

This is a real tough nut. I had used a different approach pivoting as well as consulted Jim, so what I could come up finally is that in the following view you are able to access the Service period (From Start to End Month), Actual Month of Service and Cost of Service per Month.

Though I am not able to get in between months, I hope this Helps as well as Jim's Solution. Don't hesitate to mark Helpful to both me and Jim as this was really tough for us to come up to this point too and involved lot of thinking.

Thanks

Deepak

Here is attachment in 10.2

Thanks Deepak

Very elegant - see what you did -

Jim

Thanks Jim