August 4, 2014 at 4:03 pm
I have a factsummary table that looks like the below:-
I am using DimDate Dimension from the demo analysis services project.
I have populated the dimension table accordingly. i.e uptil 2018.
I need to look into the actuals(TOTAL) for 5 years and project the numbers for the next 3 fiscal years.
I am converting an excel report into SSRS using SSAS as a data source.
I have already defined a cube, set the time dimensions. In excel, i would use the linear expresison Y=a.x + b
In SSAS world, there is an equivalent, but i need help with getting the right results.
I suspect that the calculation is not considering the range of the fiscal years.....
DateKey FiscalYear total scenario_Key
----------- ----------- ----------- -----------------
20100101 2010 19700 1
20110101 2011 19800 1
20120101 2012 19900 1
20130101 2013 19650 1
20140101 2014 19451 1
20150101 2015 NULL 2
20160101 2016 NULL 2
20170101 2017 NULL 2
--(8 row(s) affected)
WITH
MEMBER Measures.[Internet Sales Forecast] AS
LinRegPoint(
Rank( [Date].[Calendar].CurrentMember, [Date].[Calendar].CurrentMember.LEVEL.MEMBERS),
Descendants( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember.LEVEL),
[Measures].[Total],
Rank( [Date].[Calendar], [Date].[Calendar].CurrentMember.LEVEL.MEMBERS))
,FORMAT_STRING="#,0"
SELECT
DESCENDANTS( [Date].[Calendar].[Calendar Year],
[Date].[Calendar].[Year]
) ON COLUMNS,
{
[Measures].[Total],
[Measures].[Internet Sales Forecast]
} ON ROWS
FROM [Adventure Works]
Kunal Gandre
>
Snr. SQL DBA
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply