Summing all ammounts up to a specific week

[HandsFree]

Hi,
I have a fact table that has columns for [Date], [Project] and [Amount]. For every row you can see how much money (amount) is spend on a specific date on a project.

There's also a calendar table that shows for every date what week it is (YYYY-WW) and what day of the week etc.
We have a pivot table that displays for every week how much is spend on a project in that week (by just dragging project to the columns and YYYY-WW to the rows).

But now the requirement is to show for each week how much is spend in total up to that week on a project. I can't use year-to-date things (I assume) because the start of the calender year is not relevant. Projects started in 2012 and may be open for more than a year.

Hope this makes sense.
Any ideas?

thanks

 

[raj2206]

Hi, Thanks for writing!!

Could you please share some part of both the sheets.

Thanks/Raj

 

[HandsFree]

[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD]project number
[/TD]
[TD]project name
[/TD]
[TD]date
[/TD]
[TD]amount
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]project 1
[/TD]
[TD]1-7-2012
[/TD]
[TD]232,55
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]project 2
[/TD]
[TD]1-7-2012
[/TD]
[TD]109,40
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]project 1
[/TD]
[TD]2-7-2012
[/TD]
[TD]259,00
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]project 2
[/TD]
[TD]2-7-2012
[/TD]
[TD]145,10
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[HandsFree]

I'm still stuck with this. Any ideas in what direction I should look?
thanks

 

[miguel.escobar]

if you could make a numeric column for the week that would be pretty awesome. Something like 201201 (yyyyww) so that later you could do something simple like a CALCULATE using a filter with your dates table so that it can give you the amount aggregated up to that week ( Week <= [week in selection] )