YTD Based on Current System Date

  • Article

 

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

  • Anonymous
    June 01, 2011
    but when i run these query i always get null why ???

  • Anonymous
    June 01, 2011
    but when i run these query i always get null why ???

  • Anonymous
    June 01, 2011
    The comment has been removed