CODEWITHSUNDEEP
ssasmdx
whytheq
whytheq

Reputation: 35557

PARALLELPERIOD 31st back to the 30th creates empty cell

The following measure creates a gap at the end of particular months e.g. 31st of December. It is acting as expected but I didn't foresee this happening when setting up the report.

AVG(
PARALLELPERIOD
    (
    [Date].[Date - Calendar Month].[Calendar Month],
    1,
    [Date].[Date - Calendar Month].CURRENTMEMBER
    ).PARENT.CHILDREN,
[Measures].[Revenue])

In a full script against the AdventureWorks cube I've got the following example:

WITH 
    MEMBER [Measures].[Mth_DalyAvg] AS
        AVG(
            PARALLELPERIOD(
                [Date].[Calendar].[Month],
                1,
                [Date].[Calendar].CURRENTMEMBER
                ).PARENT.CHILDREN,
        [Measures].[Internet Sales Amount]
        )
SELECT
        DESCENDANTS(
            {[Date].[Calendar].[Month].&[2007]&[11],
            [Date].[Calendar].[Month].&[2007]&[12]},
            [Date].[Calendar].[Date]
            )
        ON 1,
        {
        [Measures].[Mth_DalyAvg]
        } ON 0
FROM    [Adventure Works]

Produces the following. I'd like to find a way to repeat the same value in the cell for December 31, 2007

enter image description here

Upvotes: 1

Views: 181

Answers (1)

FrankPl
FrankPl

Reputation: 13315

If you are sure you always use days, then

    AVG(
        [Date].[Calendar].CURRENTMEMBER
        .PARENT.Lag(1).CHILDREN,
    [Measures].[Internet Sales Amount]
    )

should work.

If there can be other members of the calendar hierarchy like months as well, then it will get more complex.

Upvotes: 1

Related Questions