I am developing an OLAP cube using SQL Server 2008 SSAS.
I have a dimDate table, and my fact data needs to be sliced by two different year types.
First is calendar year, that is January 1 to December 31
Second is manufacturing year of agricultural crops, which starts at July 1st and ends at June 30th
Here is how my date table looks like:
So YearG, MonthG and QuarterG represents the manufacturing values.
All is working as long as I slice the fact data straight on these fields But trying to use more sophisticated calculations such as parallel period, ytd etc does not work on this well as it calculates based on the year derived from the date key, which is the calendar year.
Here is an MDX example for a YTD calculation I perform in this cube:
AGGREGATE((ANCESTOR([Dim Date 2].[Time].Currentmember, [Dim Date 2].[Time].[Year])), [Measures].[NIS])
How can I make it show the correct aggregation for the manufacturing year? That is sum all the values of the measure from July 1st to the currently sliced time?
Thank you