Search code examples
datedynamicmdxfiscal

MDX Get Sales starting from 2 years ago to today

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

  • If you'd like the results to be across a range of dates then try StrToSet in your WHERE clause and construct in a similar way to what you are doing.

    You currently have this:

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

    Here is an example for 2 days which you can adapt to your needs:

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