Microsoft Analysis Services Cube: MDX Calculation for YTD,MTD and Today()

Version 1

    Description:

     

    While using Microsoft Analysis services cube as the data source, we cannot create YTD, MTD calculations outright using the  “Create Calculated field” but we have to use “Create Calculated member “option. The following MDX formulas use the VBA function Now() to determine the current day and calculates MTD, YTD for Current year and Previous year.

     

    Formulas

    MTD Calculation:

    //Current Year

    SUM(MTD(Strtomember("[Time]. [Hierarchy].[Date].&["+ format(now(),"yyyy-MM-ddT00:00:00")+"]")),[Measures].[Measure Name])

    //Previous Year

    SUM(MTD(Strtomember("[Time].[Hierarchy].[Date].&["+ format(DATEADD("YYYY",-1,cdate(NOW())),"yyyy-MM-ddT00:00:00")+"]")),[Measures].[Measure Name])


    YTD Calculation:


    //Current Year

    SUM(YTD(Strtomember("[Time].[Hierarchy].[Date].&["+ format(now(),"yyyy-MM-ddT00:00:00")+"]")),[Measures].[Measure Name])


    //Previous Year

    SUM(MTD(Strtomember("[Time].[Hierarchy].[Date].&["+ format(DATEADD("YYYY",-1,cdate(NOW())),"yyyy-MM-ddT00:00:00")+"]")),[Measures].[ Measure Name])

     

    //[Time]: Name of the Time dimension

    //[Hierarchy]: Name of the Hierarchy in the Time dimension

    //[Date]: Date attribute (field) in the Time dimension

    //[Measure Name]: Name of the measure on which calculation has to be created.

    // yyyy-MM-ddT00:00:00 : Date member format,Change as per your date format

     

    Today(): Calculated member

     

    Strtomember("[Time].[Date].&["+ format(now(),"yyyy-MM-ddT00:00:00")+"]")

     

    example for Today() : http://community.tableau.com/thread/136189