YTD in future

[Galactico]

Hello,


In my AS cube I've created a calculated member which returns YTD.
The calc member is part of a dimension 'Time Base' which contains the
members 'In Period' and 'YTD'. It is created by adding a column to the
fact table containing only the value 1. Then I've created the dimension
'Time Base' with a table containing 2 columns and 1 row:
key, which is 1 and name, which is 'In Period'.


The calculated member YTD has the formula:
Sum(YTD(), ([Measures].CurrentMember,[Tim­e Base].&[1]))


So now an end user can switch between 'In period' values or YTD values
for each measure.


My problem is that when you choose YTD it shows ytd-numbers for all
months, including months in the future.


How can I adapt the YTD calculated member in a way that it only shows
YTD values up until the current day?


Kind regards.

 

[Flybridge]

I tried something similar to that a while ago and got similar results. I think we looked at the same source!

I ended up using this.

Sum(PeriodsToDate([Periods].[(All)]), [Measures].[Period Amount])

where:
[Periods] is a dimension called Periods
[Period Amount] was a measure in the fact table.

Hope this works.

40 million lemmings can't be wrong........can they?

 

[Galactico]

Thanks for your answer, but I think you didnt't understand the exact problem.

The YTD formula I have works perfectly. The only problem is that for example when I make a crosstab with measures on the y axis and months on the x-axis and choose the YTD it shows YTD for all months of the year. I'd like to see only the months that have passed or the current month.

Current Situation: (YTD)
jan feb mar apr may jun jul aug sep oct nov dec
Sales 10 20 30 35 40 40 40 40 40 40 40 40

Preferred Situation: (YTD)
jan feb mar apr may
Sales 10 20 30 35 40

Hope this helps.