I am having some difficulty with a a stacked bar chart using fiscal year with drill down in to fiscal quarter and fiscal month.
First thing is to limit the bars on the chart dynamically to AddYears(Today(),-1) through AddYears(Today(), +5). I was able to get this working using IF statements, but I thought there maybe was a better (faster/optimal) way?
My Fact table looks something like this:
Date1
Date2
Date3
Date4
...
DateX
Flag1
...
FlagX
Category
Sale_Amnt1
Sale_Amnt2
The chart should be Sale_Amnt stacked by category where flag =1 and Date1 < Date2 and Date1 is in the display range (2010-2016). For Example:
Date1 = 3/3/2011
Date2 = 3/3/2013
SaleAmt = $10
Category = A
Then $10 should show up for each bar 2011, 2012, 2013 stacked in the "A" segment
I have it set up now so my Dimension is Date1 and have a feeling this is incorrect...
My Expressions are nested IF statments, e.g.:
IF(flag=1 and Date1 < Date2, IF(Category ='A', IF(SomeotherCondition, SaleAmnt1, Saleamnt2)))
I also have built a master calendar of fiscal dates 1995 through 2020. But because there are many date fields in my fact table I was not sure how to link the calendar to the fact table.
Currently the chart is very slow and the data is coming out incorrectly bucketed.
