SSAS: Create a Calculated Measure always getting the value for “Yesterday”

If you want to create a calculated measure in SSAS (SQL Server Analysis Services) that will always get the value for yesterday, this is what I have come up with:

 

CREATE MEMBER CURRENTCUBE.[MEASURES].[My Yesterday Measure]
 AS (StrToMember(‘[Time].[Calendar Date].&[‘+ VBA!Format(vba!dateadd(“d”, -1, vba![date]()),’yyyy-MM-dd’) +’T00:00:00]’),[Measures].[My Cool Value]),
FORMAT_STRING = “$#,##0.000”,
VISIBLE = 1  ;

What is really cool is you can test this before you even make the measure, using an MDX query:

 

WITH
   MEMBER Measures.[Yesterday Measure Test1] 
   AS ([Time].[Calendar Date].&[2007-07-09T00:00:00],[Measures].[My Cool Value])
   MEMBER Measures.[Yesterday Measure Test2] 
    AS (StrToMember(‘[Time].[Calendar Date].&[‘+ VBA!Format(vba!dateadd(“d”, -1, vba![date]()),’yyyy-MM-dd’) +’T00:00:00]’),[Measures].[My Cool Value]),FORMAT_STRING = “$#,##0.000”
SELECT {
Measures.[Yesterday Measure Test1]
,Measures.[Yesterday Measure Test2]
} ON COLUMNS
FROM [My Cube]

and now you can take that calculated measure and tweak for any days back (30, etc)

Cool! :)

Advertisements

One thought on “SSAS: Create a Calculated Measure always getting the value for “Yesterday””

  1. Hi Stevie,

    I came across your post and it looks just the ticket for what I need. I’ve got a simple cube with just 2 days worth of data at the moment. This is what I tried:

    WITH
    MEMBER Measures.[Today]
    AS ([Dim Time].[ASOFDATE].&[2009-06-02T00:00:00],[Measures].[GBPEQUIV])

    MEMBER Measures.[Yesterday]
    AS (StrToMember(‘[Dim Time].[ASOFDATE].&[‘
    + VBA!Format(vba!dateadd(“d”, -1, vba![date]()),’yyyy-MM-dd’)
    + ‘T00:00:00]’),
    [Measures].[GBPEQUIV])
    SELECT {
    Measures.[Today]
    ,Measures.[Yesterday]
    } ON COLUMNS
    FROM [CSAR VIEW]

    So I’m looking for data for 02-Jun and 01-jun. I get the “Today” value but “Yesterday” say it’s null even though I know the cube has data for 01 and 02 June. I tried:

    WITH
    MEMBER Measures.[Today]
    AS ([Dim Time].[ASOFDATE].&[2009-06-02T00:00:00],[Measures].[GBPEQUIV])
    MEMBER Measures.[Yesterday]
    AS ([Dim Time].[ASOFDATE].&[2009-06-01T00:00:00],[Measures].[GBPEQUIV])

    SELECT {
    Measures.[Today]
    ,Measures.[Yesterday]
    } ON COLUMNS
    FROM [CSAR VIEW]

    (i.e. – hardcoding the yesterday value and it gave me data for both days).

    Any idea what I’m doing wrong?

    Thanks in advance,
    matt

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s