MDX to display latest 13 weeks

superdeathmonkey 1 Reputation point
2020-12-16T13:58:52.46+00:00

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"")+""]"")}

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,245 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,816 Reputation points
    2020-12-17T06:58:18.537+00:00

    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

    0 comments
  2. Alexei Stoyanovsky 3,416 Reputation points
    2020-12-17T08:31:47.957+00:00
    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).

    0 comments