Hello, I am wondering if anyone can help.
In short: I am trying to make a measure to add the cumulative net difference between two fact tables and sweep the balance forward to the first month after the last period of the ACTUALS table.
I have two fact tables, ACTUALS and FORECAST, both related to a Date table, called DATES (and a wider, star schema model). Table ACTUALS has historical and current data up to March of this year and the other Table has forecast data for every month of this year and no prior year data. I have been asked to see if I can sweep the balances from the differences between the two tables into the first month of the forecast, so as to maintain the full year balance when combining actual data with forecast data in a measure.
I have the following measures (these are simple sum measures from each table and the below measures are on a table with no relationships):
Actual Minus Forecast =
//This generates the cumulative value of the variance up to the last month of the ACTUALS table
CALCULATE (
[_Sum Actual] - [_Sum Forecast],
CALCULATETABLE (
DATESYTD ( DATES[Date] ),
DATES[Dateswithtransaction]=TRUE
)
)
Cumulative Actual minus Forecast =
//This generates a cumulative value that appears to keep the total cumulative value each month for the remainder of the dates in the year.
CALCULATE (
[Actuals minus Forecast],
FILTER ( ALL ( DATES ),
DATES[Date] <= MAX ( DATES[Date] )
)
)
Here is a sample table of the output i have currently generated, plus the desired output i am unable to get:
| Date | Sum Actuals | Sum Forecast | Actual minus Forecast | Cumulative Actual minus Forecast | Desired output | |
| 01/01/2024 | 100 | 105 | 5 | 5 | 100 | Actuals |
| 01/02/2024 | 120 | 125 | 10 | 10 | 120 | Actuals |
| 01/03/2024 | 105 | 120 | 25 | 25 | 115 | Actuals |
| 01/04/2024 | | 110 | | 25 | 135 | Forecast plus 25 |
| 01/05/2024 | | 125 | | 25 | 125 | Forecast |
| 01/06/2024 | | 130 | | 25 | 130 | Forecast |
| 01/07/2024 | | 120 | | 25 | 120 | Forecast |
| 01/08/2024 | | 125 | | 25 | 125 | Forecast |
| 01/09/2024 | | 115 | | 25 | 115 | Forecast |
| 01/10/2024 | | 120 | | 25 | 120 | Forecast |
| 01/11/2024 | | 110 | | 25 | 110 | Forecast |
| 01/12/2024 | | 115 | | 25 | 115 | Forecast |
| Total | 325 | 1420 | 25 | 25 | 1430 | |
Any help would be greatly appreciated.
Thanks
