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! :)