none
MDX Date Dependent Formula RRS feed

  • Question

  • I need help writing a date dependent formula in MDX.  I am not very experienced with proper syntax, and just try to mimic other working formulas by trial and error.

    Basically, I want one formula if the date is between Nov 2017 and Apr 2019, otherwise use a different formula.  Below is what I tried, but I am not getting the desired results.

    Case when ([Effective Date].[FY Date].[Month].CURRENTMEMBER.MEMBERVALUE >= [Effective Date].[FY Date].[Month]&[FY 2017-Nov] and [Effective Date].[FY Date].[Month].CURRENTMEMBER.MEMBERVALUE <= [Effective Date].[FY Date].[Month]&[FY 2019-Apr]) then

    [Account].[Account].&[7147]-

    [Account].[Account].&[4388]-

    [Account].[Account].&[4387]-

    [Account].[Account].&[4309]-

    [Account].[Account].&[4312]+

    [Account].[Account].&[5625]+

    [Account].[Account].&[7408]

    else

    [Account].[Account].&[7147]-

    [Account].[Account].&[4388]-

    [Account].[Account].&[4387]-

    [Account].[Account].&[4309]-

    [Account].[Account].&[4312]

    Thursday, November 14, 2019 7:13 PM

All replies

  • Hi jiffbednar,

    Welcome to SQL Server Analysis Services community forum.

    Per your description, I know that you are new to MDX syntax and have interest in studying MDX expression. In fact, your syntax is invalid in MDX expression. Could you please use some sample data to support your description? If you could provide the schema of your model, it would be better.

    Thanks for your understanding and support.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 15, 2019 2:20 AM
  • I've been struggling with this still and have not found a solution.  I have made some progress though.  I am mainly an accountant, and not a programmer, so I might not be using the right terminology, but here goes.

    Here is my formula as it is now:

    Case when ([Effective Date].[FY Date].CURRENTMEMBER.MemberValue >= [Effective Date].[FY Date].[Month].&[FY 2017-Nov].MemberValue and
              [Effective Date].[FY Date].CURRENTMEMBER.MemberValue <= [Effective Date].[FY Date].[Month].&[FY 2019-Apr].MemberValue) then
    [Account].[Account].&[4325]+
    [Account].[Account].&[7147]-
    [Account].[Account].&[4388]-
    [Account].[Account].&[4387]-
    [Account].[Account].&[4309]-
    [Account].[Account].&[4312]+
    [Account].[Account].&[5625]+
    [Account].[Account].&[7408]
    else
    [Account].[Account].&[4325]+
    [Account].[Account].&[7147]-
    [Account].[Account].&[4388]-
    [Account].[Account].&[4387]-
    [Account].[Account].&[4309]-
    [Account].[Account].&[4312]
    end

    It actually returns values now and not Error!, but it is not the desired result.  We have an Effective Date dimension which is broken down by Fiscal Year [FY Date], then by Quarter, then by Month [Month], then by Day.  It seems like the formula works at the [FY Date] level, but not when looking at the [Month], it seems like it only evaluates the Fiscal Year correctly, and ignores the Month part of the criteria using greater than or less than.

    Someone suggested to me that I use INTERSECT rather than greater than or less than in my formula.  I have only used INTERSECT in the past to determine if a department belongs within a certain entity in our Organization dimension.  I have never used INTERSECT with dates.  Is that the way to get the correct result?  What would the syntax be?

    Tuesday, December 3, 2019 7:29 PM