MDX to display latest 13 weeks

Question

I am currently querying data to pull latest 13 weeks based off dates but i want to base this off weeks rather than dates. I am currently using the following MDX but keeps showing a blank table

{({STRTOMEMBER(""[Date].[Week].&[""+FORMAT(NOW(),""YYYYWW"")+""]"").Lag(13):STRTOMEMBER(""[Date].[Week].&[""+FORMAT(NOW(),""YYYYWW"")+""]"")}

Answer 1

Hi,
Try this :

WITH  
SET LastWeek AS ClosingPeriod([Date].[Calendar Weeks].[Calendar Week]  
, [Date].[Calendar Weeks].DefaultMember)  
SET Last13Weeks AS {LastWeek.Item(0).Lag(12):LastWeek.Item(0)}  
SELECT Last13Weeks ON 0  
FROM [Adventure Works]  
WHERE ([Measures].[Sales Amount]);  

Regards,
Lukas

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 2

1. If you process the Date dimension often enough, you could add a 'current week' attribute and thus lose the StrToMember call
2. 13 weeks is lag(12)
3. I suggest the LastPeriods function for clarity and to optimize StrToMember if they stay

First things to debug are doubled quotes "" and the format string (it's case sensitive).