March 7, 2015 at 12:40 am
I have a problem when I write scopes statement to calculate Calendar Year-To-Date Values in cube using SSAS.
Here is my Date Dimension and its hierarchy: enter image description here enter image description here
In measures group, I have a measure named [Sales Amount] which is the sales amount of each item in date. I want to calculate year-to-date value. Here is my scope statement but it don't work well.
SCOPE(MEASURES.[YTD Sales]); --Calendar YTD SCOPE([DimDates].[Year-Qtr-Month-Day].MEMBERS, [DimDates].[Year].[Year].MEMBERS); THIS = AGGREGATE( PERIODSTODATE([Date].[Year].[Year], [Date].[Year].CURRENTMEMBER), [Measures].[Sales Amount]); END SCOPE; END SCOPE;
Thanks for any suggestion.
March 9, 2015 at 11:12 am
Try having a look here: http://www.sqlbi.com/tools/datetool-dimension/
March 9, 2015 at 5:18 pm
I'd recommend reading Chris Webb's blog posts on the subject: https://cwebbbi.wordpress.com/2013/05/23/using-scoped-assignments-to-show-calendar-and-financial-year-to-date-values-in-the-same-calculated-measure/
If you're still having issues after that, please post your results (and expected results) so that we can get a better picture of what is happening.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply