Time Calculations: Formatting percentage calculations in multidimensional cubes

I received this little tip from a colleague and thought I'd pass it on hoping to be of help to someone else.

Problem: Dynamic time calculations in multidimensional cubes are not by nature formatted.  It would be nice to at least return percent time calculations as percentages.

Problem Illustrated: In the screen print below, [Prior Period % Change], [YTD % Change] ... columns are presented in a pivot table by default without a percentage sign.

Desired Result: Automatically format percentage calculations appearing in a pivot table with a percentage sign.

Assumption: You are familiar with creating MDX time calculations that can be applied against any measure in a multidimensional cube.

Solution: Scope for your percentage time calcs at the end of your MDX script, and format them as a percent.
--format percentages
SCOPE(
    {[Date Calculations].[Date Calculation Name].[Prior Period % Change],
     [Date Calculations].[Date Calculation Name].[YTD % Change],
     [Date Calculations].[Date Calculation Name].[QTD % Change from Prior Year],
     [Date Calculations].[Date Calculation Name].[QTD % Change from Prior Quarter],
     [Date Calculations].[Date Calculation Name].[MTD % Change from Prior Year],
     [Date Calculations].[Date Calculation Name].[MTD % Change from Prior Month],
     [Date Calculations].[Date Calculation Name].[WTD % Change from Prior Year],
     [Date Calculations].[Date Calculation Name].[WTD % Change from Prior Week]}
    );
    THIS = ([Measures].currentmember);
    FORMAT_STRING(this)="Percent";
END SCOPE;

Result Illustrated:  Without having to format the percentage calculation columns or rows in Excel, they will come in formatted when selected from PivotTable Fields. 

Important Note!!  This only works with NEW  pivot tables.  If your pivot table or chart has already been created, doing a <Data Ribbon --> Refresh> in Excel will not update the formatting.  You'll see your new MDX work when you <Insert> new.

Detail MDX Script Illustrated:
SCOPE ([Date Default].[Y-Q-M-D Hier].MEMBERS);  
--YEAR CALCULATIONS
    --Year To Date

    [Date Calculations].[Date Calculation Name].[YTD] =
     AGGREGATE
     (
        {[Date Calculations].[Date Calculation Name].[Current Period]} *
          PERIODSTODATE
          (
            [Date Default].[Y-Q-M-D Hier].[Year],
            [Date Default].[Y-Q-M-D Hier].CURRENTMEMBER
          )
     );           

    --Year to Date Prior Year
    [Date Calculations].[Date Calculation Name].[YTD Prior Year] =
     AGGREGATE
     (
        {[Date Calculations].[Date Calculation Name].[Current Period]} *
        PERIODSTODATE
        (
           [Date Default].[Y-Q-M-D Hier].[Year],
           PARALLELPERIOD
           (
              [Date Default].[Y-Q-M-D Hier].[Year],
              1,
              [Date Default].[Y-Q-M-D Hier].CURRENTMEMBER
        )
     )
     );  
END SCOPE; 

--PRIOR PERIOD & YEAR
    --Prior Period
    [Date Calculations].[Date Calculation Name].[Prior Period]=
    AGGREGATE
        ([Date Default].[Y-Q-M-D Hier].CurrentMember.PrevMember,
        [Date Calculations].[Date Calculation Name].[Current Period]);  

​--calculate change
[Date Calculations].[Date Calculation Name].[Prior Period Change] = 
    [Date Calculations].[Current Period] - [Date Calculations].[Prior Period];           

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


--calculate % change
[Date Calculations].[Date Calculation Name].[Prior Period % Change] = 
    DIVIDE (([Date Calculations].[Date Calculation Name].[Current Period] - [Date Calculations].[Date Calculation Name].[Prior Period]), 
         [Date Calculations].[Date Calculation Name].[Prior Period],0);            

[Date Calculations].[Date Calculation Name].[YTD % Change] = 
    DIVIDE (([Date Calculations].[Date Calculation Name].[YTD] - [Date Calculations].[Date Calculation Name].[YTD Prior Year]), 
         [Date Calculations].[Date Calculation Name].[YTD Prior Year],0);         

--format percentages
SCOPE(
    {[Date Calculations].[Date Calculation Name].[Prior Period % Change],
     [Date Calculations].[Date Calculation Name].[YTD % Change]}
    );
    THIS = ([Measures].currentmember);
    FORMAT_STRING(this)="Percent";
END SCOPE;