Creating a Dynamic YTD calculation in MDX

I have a YTD calculation that I want to make dynamic based on the real current date.

This is the regular YTD formula:

Sum(YTD([Date].[Calendar Hierarchy].CurrentMember),[Measures].[Planned Orders])

This is the YTD formula hard coded with the current date:

Sum(YTD([Date].[Calendar Hierarchy].[Calendar Year].&[2007].&[2007Q1].&[2007-01].&[2007-01-30T00:00:00]),[Measures].[Planned Orders])

The hard coded date works but I need the date to be dynamic. I’m not sure how to get it to be based from the current date.

I’ve been experimenting with using the NOW() function to return the date but I can’t get the syntax correct.

Thank you.

David

Hmmm i think you can find your answer here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

|||

The article is good but it doesn't address my need to make the date part of the calculation relative and based on the current date (down to the day level).

David

|||

Perhaps this thread can help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=996269&SiteID=1

Regards

Thomas Ivarsson

|||

Thomas,

Unfortunately, I have budget like future date information in the cube. As a result, I probably need to develop a dynamic way of capturing the current date.

Would this possible scenario work using your suggestion:

Suppose I created a new fact table with one record where the date changed every day. I have a measure column called "Sales " with a value of 1.

I add this fact table to the cube and rewrite your named set:

Tail(Filter([Time].[Time_Calendar].[Month].Members,(Time.[Time_Calendar].Currentmember,[Measures].[lSales])>0))

This would make the date always based on the current date. However, I'm not sure if would filter out other records that I need to show.

David

|||

If you have a measure that is updated daily, like actual sales, my solution will work even if you have a budget measure that points to future dates.

This link have some other suggestions.

http://support.dspanel.com/help43/Web_Part/Examples/MDX_Examples.htm

HTH

Thomas Ivarsson