YTD Based on Current System Date


 

with member [Date].[Calendar].[currentyear] as aggregate(ytd((strtomember(“[Date].[Calendar].[Date].&[“+format(now(),”yyyyMMdd”)+”]”))))

member [Date].[Calendar].[prevyear] as aggregate(ytd(parallelperiod([Date].[Calendar].[Calendar Year] ,1,(strtomember(“[Date].[Calendar].[Date].&[“+format(now(),”yyyyMMdd”)+”]”)))))

select

{

[Date].[Calendar].[currentyear] ,[Date].[Calendar].[prevyear]

}on 0, [Measures].[Internet Sales Amount] on 1 from [Adventure Works]

Well, my customer wanted to pass fix start date member and get the result so changed query as per Customer’s requirement.

From your application you can pass value but keep in mind you need to use strtomember if you are passing any string value.

WITH MEMBER [Measures].[Current YTD] AS

SUM({[Date].[Calendar].[Date].&[20030101]:strtomember(“[Date].[Calendar].[Date].&[“+format(now(),”yyyyMMdd”)+”]”)},[Measures].[Internet Sales Amount])

MEMBER [Measures]. [Last YTD]   AS

SUM({[Date].[Calendar].[Date].&[20020101]:strtomember(“[Date].[Calendar].[Date].&[“+format(dateadd(“yyyy”,-1,now()),”yyyyMMdd”)+”]”)},[Measures].[Internet Sales Amount])

SELECT {[Measures].[Current YTD], [Measures]. [Last YTD]  } ON 0

from [Adventure Works]

Comments (3)

  1. nice post says:

    but when i run these query i always get null why ???

  2. nice post says:

    but when i run these query i always get null why ???

  3. Karan Gulati says:

    Hey, well Now is showing null may be for Current Date you dont have data.

    Check in the cube for Dates for which you have data, for testing purpose change the system of ur test / dev server to dates for which you have data and execute this mdx, i will definately work 🙂

    Thanks for your comments.

    Karan Gulati

    Support Escalation Engineer, Microsoft BI Team