Time Calculations: Returning NULL Values for Future Days

Sometimes users do not want to see time calculations for days in the future -- even if there is data.  To “nullify” date calculation for days in the future, follow these steps:

1.       Create an IsFutureDate named calculation in your date dimension.
           CASE WHEN FullDateAlternateKey > GetDate() THEN 1 ELSE 0 END

2.       Add the new attribute to your date dimension.

3.       Add the following script to the end of your MDX time calculations.

--set future date calculations to NULL

SCOPE ([Date Default].[Is Future Date].&[1],

       [Date Ship].[Is Future Date].&[1],

       [Date Due].[Is Future Date].&[1]);           

    [Date Calculations].[Date Calculation Name].[YTD] = NULL;           

    [Date Calculations].[Date Calculation Name].[YTD Change] = NULL; 

    [Date Calculations].[Date Calculation Name].[YTD % Change] = NULL;

    [Date Calculations].[Date Calculation Name].[YTD Prior Year] = NULL;

    [Date Calculations].[Date Calculation Name].[MTD] = NULL;         

    //add additional time calculations here as needed

END SCOPE;

4.       Verify the results.  In the screen print below, the current date was 7/24/2015.  All future days returned empty cell values.