I am using a sum(ytd) and sum(periodstodate) to calculate linked and trailing investment performance returns. However, in the data that we are analyzing, the history is different for each investment and / or portfolio. The calculation is not valid if there is not data for every period in the time series. Therefore, if a value in the period is null, I want the result to be either a null or an error that the user understands to mean there are not enough periods for that selection to calculate that measure.
We have been using the calculation without any special accomodations and the results properly displayed either blank or with an error, depending on where the results were being viewed (either in the cube browser in analysis services or through a user front end that connected to the cube). However, sometime in the past several months, a change (such as a fix pack) occurred behind the scenes and now the calculation is providing a value (it is now ignoring the Null).
I have been unsuccessful at finding the correct logic to these calculations so that if a null in the data is encountered by the calculation, there will be a null or error. I have come up with a workaround when I have specific time periods I am summing, but when using the YTD or PeriodsToDate function, the workaround is either not straightforward or is too cumbersome since some of calculations can eventually have 240 periods.
Thank you for any suggestions.
Please have a look in the link below if this may help you .
Anil Blog post:
http://bidn.com/blogs/DevinKnight/ssis/1587/ssas-preserve-measure-null-values By Mosha
This is a very interesting blog post, but it doesn't address my issue. As I noted above, I am summing a group of numbers by using sum(ytd([Time].[Fiscal]),[Measure].[xxxx]. If I was summing a finite or small set of numbers, I can easily add IIF logic, which I have done for the 3 month trailing and 12 month trailing. However, the ytd is a changing number of months and I haven't figured out how to check for any null in the series, whether it is ytd or one of a total of 240 months worth of data. Any suggestions are appreciated.