MDX Get Sales dynamically starting from 2 years ago to today

Daniel 41 Reputation points
2021-03-10T18:18:28.46+00:00

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]"))}
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,259 questions
0 comments No comments
{count} votes

Accepted answer
  1. Alexei Stoyanovsky 3,416 Reputation points
    2021-03-11T08:11:07.09+00:00

    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).

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,821 Reputation points
    2021-03-11T08:19:49.627+00:00

    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