SQL BI

My Experience - Troubleshooting SQL BI Stack

YTD Based on Current System Date

YTD Based on Current System Date

  • Comments 3

 

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
  • but when i run these query i always get null why ???

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

  • 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

Page 1 of 1 (3 items)
Leave a Comment
  • Please add 3 and 4 and type the answer here:
  • Post