I have calculated date "Forecasting Date" : If [Units]>=0.001 Then [Start Date ] else [End Date] end. Start Date and End Date are Date Parameter from 2012-2020.
I have several additional Parameters called "2012 What if % x, 2013 What if % x, 2014 What if % x" .....and so on for each year so that I can vary the forecasted trends in each individual year. these parameters are integer 100-300 step size is 1.
I have several calculated measures(Year Units) which build on each other starting with using 2012 as baseline data, "2012 Units, 2013 Units, 2014 Units" etc etc
[2012 Units] = [Total Forecasting Units Base]*[2012 What if % x]/100
[2013 Units] = [2012 Units]*[2013 What if % x]/100
[2014 Units] = [2013 Units]*[2014 What if % x ]/100 ......and so forth you get the picture.
I then combined each individual Year Units measure in one measure like this: called[Forecast Units]
If (DATEPART("year",#1/1/2012#)= 2012) and not ISNULL([2012 Units]) then [2012 Units]
elseif (DATEPART("year",#1/1/2013#)= 2013) and not ISNULL([2013 Units]) then [2013 Units]
elseif (DATEPART("year",#1/1/2014#)= 2014) and not ISNULL([2014 Units]) then [2014 Units]
elseif (DATEPART("year",#1/1/2015#)= 2015) and not ISNULL([2015 Units]) then [2015 Units] ..............etc. etc. ........
My problem is this: If I stack up each individual Year Units in rows on measure values shef and place my Forecasting Date" as Column The measure values all populate under Forecast Date 2012, however the values are right and the parameter controls work perfectly on each one building on the next...
If I use my combined measure it solves the problem and places the values out across in the appropriate column of Forecast year, however the values are incorrect and only the 2012 and 2013 What if % x parameters work.
I have tried this back and forth with both the combined and individual measures trying to figure out if I could also make the "Year Units" more Forecast Date specific in an IF statement such as happens in the combined [Forecast units], which doesnt work and also tried different variations of the date specifics for the [Forecast units] which always results in the "cannot mix aggregate and non aggregate" error. I tried using MIN/MAX but that doesn't work b/c I have multiple years. I tried referencing the date range inside the Start Date & End Date Parameters and that didn't work.........
What perplexes me is that one half of it works in one view and the other in the other but not both together as they should be. I can either have correct data and incorrect date column or visa versa..... I have toyed with this long enough and surfed around wasting a day trying to solve this. Please Help if anyone has run into this or knows what I am talking about.
I cannot post a sample workbook b/c data is extremely confidential and i have to run it through compliance which will take until next year to get through or i would have posted one sorry.