So I have a system where users can forecast up until the 18th day following the end of a month. However, during the month they are entering actuals on a day to day basis.
So say the current date is 12th September 2012. I have a forecast for this month of 100 units and I already have actuals entered into the system of 25 units.
Because the 18th October hasn't been reached I would report September as having a forecast figure of 100 units. However, the users want to also be able to see the 25 units that they have entered to date.
I have two measures in my report, one for Actuals and one for Forecasts. If I allow the actuals for September to appear in the report then I will be displaying a figure of 125 units which is obviously incorrect and will make my year total also incorrect.
I considered adding a new measure "Transient Actuals", that would only be used in the current month and wouldn't accumulate, but this seems crazy!
I don't think the technologies really matter at all but I am using a SQL Server database and the report is an Analysis Services cube.
Surely this must be a common problem?
Okay - I do have a schema but it is very, very complicated and there are multiple layers between the report and the database tables. However, I can give you a simple SQL example.
--Actuals and Forecast Tables
DECLARE @Actuals TABLE (
[Month] INT,
Value NUMERIC(19,2));
DECLARE @Forecast TABLE (
[Month] INT,
Value NUMERIC(19,2));
--Pretending we are in Month #3 put in some dummy data
INSERT INTO @Actuals VALUES (1, 100);
INSERT INTO @Actuals VALUES (2, 120);
INSERT INTO @Actuals VALUES (3, 10);
INSERT INTO @Forecast VALUES (1, 90);
INSERT INTO @Forecast VALUES (2, 90);
INSERT INTO @Forecast VALUES (3, 90);
--Calculate the latest actuals period (this would usually be time-based)
DECLARE @LatestActualsPeriod INT = 2;
--Now report the data to the user
SELECT
[Month],
'Actuals' AS Source,
Value
FROM
@Actuals
WHERE
[Month] <= @LatestActualsPeriod
UNION ALL
SELECT
[Month],
'Forecast',
Value
FROM
@Forecast
WHERE
[Month] > @LatestActualsPeriod;
Now the results of that will be a table with the following data:
Month Source Value
1 Actuals 100.00
2 Actuals 120.00
3 Forecast 90.00
So where do I put the 10.00 actuals in Month 3 without making the totals incorrect and still displaying the forecast for the same month somewhere?
Okay, thanks to Jeremy I think I have the answer... the problem is that I have data like this in my report:
Row Labels Actual Forecast Total
2012 23.840 18.840 42.680
2012/Jan 3.580 0.000 3.580
2012/Feb 3.520 0.000 3.520
2012/Mar 4.000 0.000 4.000
2012/Apr 3.350 0.000 3.350
2012/May 3.440 0.000 3.440
2012/Jun 3.090 0.000 3.090
2012/Jul 2.860 0.000 2.860
2012/Aug 0.000 4.990 4.990
2012/Sep 0.000 3.500 3.500
2012/Oct 0.000 4.130 4.130
2012/Nov 0.000 2.710 2.710
2012/Dec 0.000 3.510 3.510
Grand Total 23.840 18.840 42.680
This is an Analysis Services cube so I can't have anything like "90 Actual/ 10 Forecast" displaying, each cell must contain a number. However, I have another solution. If I add a new dimension with two members I can use this to control what is displayed.
So the new dimension will have two choices, "Show All" or "Show Relevant" (I might need to work on the names). If the user has the "Show All" member selected then they will see any Actuals and Forecasts that the system holds. So this means that their total will be meaningless as it will include forecasts AND actuals for months up to and including the present month and just forecasts for future months.
However, if the user selects the "Show Relevant" member then they will only see the Actuals for months that haven't been completed and forecasts for future months (as shown above).
This will mean that every report will need to include this dimension and force the users to pick one of the members or their figures will be totally meaningless so it isn't the most elegant solution.
I'm making a bunch of assumptions here, but hopefully this will be useful here. I took your query and am showing you some other ways to do it that I think could help in your problem of showing the data in an easier to read way, if I'm understanding the problem correctly.
In the first example I'm making the assumption that you always put the date as the first of the month. If not then it could get a little trickier, or you could use DATEPART(MONTH, ... to make it work similarly as if you were storing it as an INT. I'm not assuming that every month will have a forecasted value or an actual value.
In the second example, to make it easier, I assumed that you want to show only the actuals up to the latest month with actuals. I then assume that you want the forecasted value and actuals (if there are any) for that month and beyond. I'm also assuming that there is a forecasted value for each month in the second query.
If any of these assumptions are incorrect, then the query can be modified further to handle that.
--Actuals and Forecast Tables
DECLARE @Actuals TABLE (
[Month] DATE,
Value NUMERIC(19,2));
DECLARE @Forecast TABLE (
[Month] DATE,
Value NUMERIC(19,2));
INSERT INTO @Actuals VALUES ('20120801', 100);
INSERT INTO @Actuals VALUES ('20120901', 120);
INSERT INTO @Actuals VALUES ('20121001', 10);
INSERT INTO @Forecast VALUES ('20120801', 90);
INSERT INTO @Forecast VALUES ('20120901', 90);
INSERT INTO @Forecast VALUES ('20121001', 90);
INSERT INTO @Actuals VALUES ('20121101', 50);
INSERT INTO @Forecast VALUES ('20121201', 90);
-- If you can't have an actuals record without a forcast record, remove all the ISNULL'ing except the first ISNULL on the [Actual/Forecast] line
-- and change to LEFT JOIN instead of FULL.
SELECT
SUBSTRING(CONVERT(VARCHAR, ISNULL(F.[Month], A.[Month]), 103), 4, 999) [Report Month]
, ISNULL(CAST(A.Value AS VARCHAR(20)), '-') + '/' + ISNULL(CAST(F.Value AS VARCHAR(20)), '-') [Actual/Forecast]
FROM
@Forecast F
FULL JOIN @Actuals A
ON A.[Month] = F.[Month]
ORDER BY
[Report Month]
DELETE @Actuals
WHERE [Month] = '20121101'
DELETE @Forecast
WHERE [Month] = '20121201'
DECLARE @LatestActualsPeriod DATE = (SELECT MAX([Month]) FROM @Actuals);
SELECT
[Month] [Report Month]
, CAST(A.Value AS VARCHAR(20)) + ' Actual' [Units]
FROM
@Actuals A
WHERE
A.[Month] < @LatestActualsPeriod
UNION
SELECT
F.[Month] [Report Month]
, ISNULL(CAST(A.Value AS VARCHAR(20)), '-') + '/' + CAST(F.Value AS VARCHAR(20)) + ' Actual/Forecasted'
FROM
@Forecast F
LEFT JOIN @Actuals A
ON A.[Month] = F.[Month]
WHERE
F.[Month] >= @LatestActualsPeriod
P.S.: You put a schema in your answer, but didn't reply to my comment asking for it. If I hadn't looked back at the question again out of curiosity, I wouldn't have know you edited your question.