How do I report on completed work for a given week in TFS 2008?

Question

We're on Team Foundation Server 2008 and I'm trying to find a way to report on the change in completed work from week to week at the task level. The MDX query below works pretty well, but I'd like to get rid of need to hard code last week's date. I've tried using prevmember and parallelperiod without success, but I'm no MDX expert.

WITH 
MEMBER [Measures].[Completed Work by WI on dt1] AS
(
[Assigned To].[Person].CurrentMember,
[Work Item].[System_Id].CurrentMember,
[Date].[Year Week Date].[Week].&[2008-12-07T00:00:00],
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)

MEMBER [Measures].[Completed Work by WI on dt2] AS
(
[Assigned To].[Person].CurrentMember,
[Work Item].[System_Id].CurrentMember,
[Date].[Year Week Date].CurrentMember,
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)

MEMBER [Measures].[Completed Work] AS
[Measures].[Completed Work by WI on dt2] - [Measures].[Completed Work by WI on dt1]

SELECT
NON EMPTY
{
[Measures].[Completed Work] 
}
ON COLUMNS,

NON EMPTY
{
Filter(
([Assigned To].[Person].[Person],[Work Item].[System_Id].[System_Id],[Work Item].[System_Title].[System_Title]), [Measures].[Completed Work] >0 )
}
ON ROWS

FROM [Team System]

Answer 1

Look at the provided Work Completed report. It automatically sets one of its date fields to today minus one month.

EDIT: Just logged into my work system to double check on this. The report is actually called "Remaining Work". Go to the SharePoint portal that was created for your Team Project, and find the list of standard reports. It'll be in that list. You can export that report to file, open it in Visual Studio and see the date field logic.

EDIT2: For an MDX function to get the previous week, try a variation on this: http://social.msdn.microsoft.com/Forums/en-US/tfsreporting/thread/0a656453-eaf1-47a2-a376-cb6eaec0db51

Answer 2

@sliderhouserules - I took a look at that report and it appears it is just taking the date entered and using the strtomember function. In my query, it looks like it would be the equivalent of the line with the hard coded date with:

StrToMember("[Date].[Year Week Date].[Week].&[" + Format(DATEADD("d", -7, "2008-12-21"), "s") + "]")

This works fine. However, what I really want is to eliminate the need to hard code anything. I tried using the Now() function instead of the hard coded date. I wasn't able to get it to work, but even if I did it would still mean that I'd need to change the number of days to subtract to get back to the Sunday of the previous week. It seems like there should be an MDX function that would make this work. If not, then perhaps there is some way to modify the StrToMember line to derive the previous Sunday's date in the proper format.