MDX Get Sales dynamically starting from 2 years ago to today

Question

I'm trying to make a dynamic report that pull sales starting from the first day of the fiscal year 2 years ago up to today and rolls forward with each new fiscal year. Our fiscal years don't line up with calendar years. I have little MDX experience and am still learning.

So it should look at todays date, get the current fiscal year, subtract 2 years from it and then pull sales starting from that year up to today.

I had some difficulty just trying to get the date working correctly as I was getting errors however the below query now pulls yesterdays sales for me. I assume I need to reference [Date].[Year] as well, but I don't know how to use it to get my desired results.

SELECT 
NON EMPTY 
{ [Measures].[Gross Margin Percentage], 
[Measures].[Gross Margin Value], 
[Measures].[Sales Value], 
[Measures].[Sales Units] } 
ON COLUMNS

FROM IMR

Where     
{StrToMember("[Date].[Date].&" + Format(CDate(now()-1), "[yyyy-MM-ddT00:00:00]"))}

Answer 1

First of all, you should set up your fiscal calendar within your Date dimension. See the AdventureWorks cube for a nice example. Next you get the current fiscal year with EXISTS ( [Date].[Fiscal Year].[Fiscal Year], <StrToMember for today> ).Item(0), and your period to do the calculation over will probably be TAIL ( NULL : <current fiscal year>, 3) (two last full fiscal years and YTD current FY since there'll probably be no sales for current FY's YTG).

Answer 2

Hi,

If you have already fiscal year hierachy in the cube you could try :

WITH 
MEMBER [Measures].[2yearsAgoTillNow] AS  
    SUM(
       OpeningPeriod(
                [Date].[Fiscal].[Date],  
                 Ancestor
                    (
                        StrToMember("[Date].[Fiscal].[Date].&[" + Format(now(), "yyyyMMdd") + "]"
                        ,[Date].[Fiscal].[Fiscal Year]
                    )
                    ).lag(2)
                ) : StrToMember("[Date].[Fiscal].[Date].&[" + Format(now(), "yyyyMMdd") + "]"
        ) , [Measures].[Gross Margin Percentage]
    )  
SELECT [Measures].[2yearsAgoTillNow] ON 0
FROM IMR

If you don't have fiscal year hierachy you need to specify the offset date of the fiscal year.

Regards,
Lukas