Hello,
I am trying to create a variable which returns Year-to-Date Sales data by using the Where() function in conjunction with other time period variables I’ve created. I have two time period variables, Current Period and Start FY (fiscal year), which calculate the current period and the beginning of the fiscal year based on a column in my Time Period table, which looks like this:
Time Period Table
iPeriod – iFiscalYear – bCurrentMonth
200904 – 2009 – 0
200905 – 2010 – 0
200906 – 2010 – 1
200907 – 2010 – 0
200908 – 2010 – 0
Current Period looks as follows:
=First([To Date].[Period])Where([To Date].[Current Month] = 1)
Start FY looks as follows:
=((First([To Date].[Fiscal Year])Where([To Date].[Current Month] = 1)-1)*100) + 5
The calculations at the end of the formula ensure that the value returned ends in ‘05,’ since our fiscal year begins in May.
These formulas seem to work, since when put in their own independent table they hold the values 200906 (Current Period) and 200905 (Start FY).
The idea is to return a sum of all sales which occured in all periods greater than or equal to Start FY and less than or equal to Current Period. The code is as follows:
=Sum([Total Sales])Where( [To Date].[Period] >= [Start FY] And [To Date].[Period] <= [Current Period])Here, only the sales for the current period are returned, no other periods are included. If I remove ‘[To Date].[Period] >= [Start FY] And’, I still only get the current period’s data, not everything before. If I remove the = sign and just try using < or >, I get no data returned at all.
Replacing the variables with numbers (200906 and 200905) makes the formula work perfectly, but is not desired. Interestingly, the following behaves as you would expect:
=Sum([Total Sales])Where( [To Date].[Period] = [Current Period])
but the same formula using Start FY does not work at all (returns nothing).
=Sum([Total Sales])Where( [To Date].[Period] = [Start FY])
There’s obviously an issue with the Start FY variable, but I can’t figure out what it is, especially since it seemingly returns the correct value. That aside, using < or > and any of the variables doesn’t seem to work, meaning it will not use all sales data in that range.
I wonder if simply my implementation is incorrect and there would be a better way to accomplish the same task. Whatever advice anyone can give me, I greatly appreciate it.
I apologize for the length of the post, but I felt it was necessary to accurately describe my situation. All the more appreciative to those who stick through it!
tylerp 
(BOB member since 2009-03-31)