CODEWITHSUNDEEP
ssasmdxolap
ish1mura
ish1mura

Reputation: 1

OLAP SSAS MDX How to get measure value of previous selected/visible date

How to get previous date member only among selected/visible members of date dimension?

I've tried PREVMEMBER and LAG functions but they return previous calendar date (yesterday).

Data in OLAP cube:

DATE       | SUM
-----------------
2018-09-01 | 500
2018-09-02 | 150
2018-09-03 | 300
2018-09-04 | 777
2018-09-05 | 900
2018-09-06 | 1200
2018-09-07 | 1500

In my query I'm selecting different dates in a filter and I need to get SUM of previous visible date:

DATE       |  SUM | PREV_SUM
-------------------------------
2018-09-02 |  150 | NULL
2018-09-04 |  777 | 150 (from 2018-09-02)
2018-09-07 | 1500 | 777 (from 2018-09-04)

My MDX query:

WITH
    MEMBER PREV_MEMBER AS
        MEMBERTOSTR([dim_date].[Day Id].CURRENTMEMBER.PREVMEMBER)
    MEMBER PREV_MEMBER_LAG AS
        MEMBERTOSTR([dim_date].[Day Id].CURRENTMEMBER.lag(1))
    MEMBER PREV_SUM AS
        SUM(
            STRTOMEMBER(PREV_MEMBER),
            [Measures].[SUM]
        )
SELECT
    NON EMPTY {
        [Measures].[SUM],
        PREV_SUM,
        PREV_MEMBER,
        PREV_MEMBER_LAG
    } ON COLUMNS,
    NON EMPTY {(
        [dim_date].[Day Id].ALLMEMBERS
    )} ON ROWS
FROM (
    SELECT ({
        [dim_date].[Day Id].&[20180902],
        [dim_date].[Day Id].&[20180904],
        [dim_date].[Day Id].&[20180907]
    }) ON COLUMNS
    FROM [cub_main]
)

My result (returns yesterday):

DATE     | SUM  |  PREV_SUM | PREV_MEMBER                     | PREV_MEMBER_LAG
--------------------------------------------------------------------------------------------
20180902 | 150  |       500 | [dim_date].[Day Id].&[20180901] | [dim_date].[Day Id].&[20180901]
20180904 | 777  |       300 | [dim_date].[Day Id].&[20180903] | [dim_date].[Day Id].&[20180903]
20180907 | 1500 |      1200 | [dim_date].[Day Id].&[20180906] | [dim_date].[Day Id].&[20180906]

How can I get PREV_SUM only among selected/displayed members?

Upvotes: 0

Views: 362

Answers (2)

ish1mura
ish1mura

Reputation: 1

WITH
    // Create custom set
    SET SSS AS
        {
            [dim_date].[Day Id].&[20180902],
            [dim_date].[Day Id].&[20180904],
            [dim_date].[Day Id].&[20180907]
        }

    // Find current date member rank in custom set
    // Decrement index of current member by 2
    // Use ITEM function
    MEMBER PREV_MEMBER AS
        SETTOSTR(SSS.ITEM(RANK([dim_date].[Day Id].CURRENTMEMBER, SSS)-2))
    MEMBER PREV_SUM AS
        SUM(
            STRTOSET(PREV_MEMBER),
            [Measures].[SUM]
        )
SELECT
    NON EMPTY {
        [Measures].[SUM],
        PREV_MEMBER,
        PREV_SUM
    } ON COLUMNS,
    NON EMPTY {(
        // Use custom set in rows as a date filter
        SSS
    )} ON ROWS
FROM [cub_main]

Upvotes: 0

whytheq
whytheq

Reputation: 35557

Try creating a custom set in your WITH clause. The set will be made up of the dates.

Then use the GENERATE function to iterate over the set. I think lag should then use only the dates in the set.

(Apologies I am away from a PC so unable to test)

Upvotes: 0

Related Questions