MDX Date ranges

  • Heloo all. Looking for help with some calculated measures. See the attached code below. It runs, but isn't returning the expected data results. What I am trying to get to with the calculated measures is this:

    [Measures].[Next 7 Days of POs] = day 0 (current day)  to day 7,

    [Measures].[Next 14 Days of POs] = day 8 to day 14

    [Measures].[Next 21 Days of POs] = day 15 to day 21

    [Measures].[Next 28 Days of POs] = day 22 to day 28

    [Measures].[Greater than 28 Days of POs] > 28 days

    Appreciate any guidance. I do need to keep this in one query.

    WITH 

    MEMBER [Measures].[Next 7 Days of POs] AS
    SUM(LASTPERIODS(-7, EXISTS([Dates].[Date].[Date].Members, [Dates].[Absolute Current Day].&[Y] )
    (0).Lag(-7)),[Measures].[Quantity Open - P])

    MEMBER [Measures].[Next 14 Days of POs] AS
    SUM(LASTPERIODS(-7, EXISTS([Dates].[Date].[Date].Members, [Dates].[Absolute Current Day].&[Y])
    (0).Lag(-7)),[Measures].[Quantity Open - P])

    MEMBER [Measures].[Next 21 Days of POs] AS
    SUM(LASTPERIODS(-7, EXISTS([Dates].[Date].[Date].Members, [Dates].[Absolute Current Day].&[Y])
    (0).Lag(-21)),[Measures].[Quantity Open - P])

    MEMBER [Measures].[Next 28 Days of POs] AS
    SUM(LASTPERIODS(-28, EXISTS([Dates].[Date].[Date].Members, [Dates].[Absolute Current Day].&[Y])
    (0).Lag(-7)),[Measures].[Quantity Open - P])

    MEMBER [Measures].[Greater than 28 Days of POs] AS
    SUM(LASTPERIODS(-700, EXISTS([Dates].[Date].[Date].Members, [Dates].[Absolute Current Day].&[Y])
    (0).Lag(0)),[Measures].[Quantity Open - P])

    MEMBER [Measures].[Past Due POs] AS
    SUM(LASTPERIODS(700, EXISTS([Dates].[Date].[Date].Members, [Dates].[Absolute Current Day].&[Y])
    (0)),[Measures].[Quantity Open - P])

    SELECT NON EMPTY
    {
    [Measures].[Quantity Open - P],
    [Measures].[Quantity On Hand - I],
    [Measures].[Next 7 Days of POs],
    [Measures].[Next 14 Days of POs],
    [Measures].[Next 21 Days of POs],
    [Measures].[Next 28 Days of POs],
    [Measures].[Greater than 28 Days of POs],
    [Measures].[Past Due POs]
    }

    ON COLUMNS, NON EMPTY {(
    [Items].[Item Number 02].CHILDREN,
    [Item Branch].[Business Unit Code].CHILDREN
    ) }

    ON ROWS FROM ( SELECT ( {
    [Items].[SRP 1 - Type].&[Parts & Accessories]
    } )
    ON COLUMNS FROM ( SELECT ( {
    [Item Branch].[Business Unit Code].&[ P1],
    [Item Branch].[Business Unit Code].&[ DMT]
    } )
    ON COLUMNS FROM [Buyer]))

    WHERE (
    [Item Branch].[Item Number 02].&[5273630]
    )
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply