• Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About
Microsoft Data & AI

SQL Server Analysis Services

Time Calculations: Returning NULL Values for Future Days

7/27/2015

0 Comments

 
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

Picture
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.
Picture
0 Comments



Leave a Reply.

    Categories

    All
    Cache
    DBA
    Excel
    Extended Events
    HOLAP
    MOLAP
    Multidimensional
    ROLAP
    Server Mode
    SSAS
    Tabular

    RSS Feed

    Tweets by @SqlServerSME
Powered by Create your own unique website with customizable templates.
  • Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About