I have date parameter like
[Time].[Year - Quarter - Date].[Year].&[2015-01-01T00:00:00]
that shows when client's fiscal year starts. So month and year can be changed.
Depending on this I need to create query that will calculate linear regression (LinRegPoint function) by fiscal years. The problem is in cube's FiscalYear dates, they have fixed start month.
WITH
MEMBER [Average Fees Per Copy] AS
LinRegPoint
(
Rank
(
[Time].[Year - Quarter - Date].CurrentMember
,[Time].[Year - Quarter - Date].[Year].MEMBERS
)
,[Time].[Year - Quarter - Date].[Year]
,
[Measures].[Spend] / [Measures].[Copies]
,Rank
(
[Time].[Year - Quarter - Date].CurrentMember
,[Time].[Year - Quarter - Date].[Year].MEMBERS
)
)
SELECT
{[Average Fees Per Copy]} ON COLUMNS
,[Time].[Year].MEMBERS ON ROWS
FROM [spend];
Here is my query, it calculates predicted values and based on cube's Fiscal Years values (that always starts on 6-th month). But how to cut cube's years on years which starts from my month in given parameter and then find linregpoint value for current client's fiscal year where calculation will be based on this cut values?
At the moment I didn't found solution for slicing time axis in query according to given year start date, so I use a workaround for this problem.
The main idea is to create custom date\time dimension and link it through many-to-many table to calendar dimension. This calendar table should store all calendars with different start year date. So your SSAS datasource may be simillar to something like this:
Then you just create measure from M2M table and use it in dimensions with date attributes with M2M relationship.
To not to store all calendar-specific data in relational DB you can imrove this solution by creating views that will calculate all on request so all this data will exists only in SSAS.
You can find more info here:
