Forum Discussion
Helper ISeparate date tables?
Helper IHi Ashish_Mathur ,
Thank you for your reply. Below is a sample date set:
| Date Input | Fiscal Month | Fiscal Year | Fiscal Quarter | EOM Sales Date | Forecast Name | Forecast Date | Forecasted Amount |
| Revenues | Jul | 2021 | Q1 | 31-Jul-20 | Dec_ forecast | 9-Dec-19 | € 1,575,000.00 |
| Revenues | Aug | 2021 | Q1 | 31-Aug-20 | Dec_ forecast | 9-Dec-19 | € 1,507,500.00 |
| Revenues | Sep | 2021 | Q1 | 30-Sep-20 | Dec_ forecast | 9-Dec-19 | € 1,590,000.00 |
| Revenues | Jul | 2021 | Q1 | 31-Jul-20 | Jan _ forecast | 13-Jan-20 | € 1,522,500.00 |
| Revenues | Aug | 2021 | Q1 | 31-Aug-20 | Jan _ forecast | 13-Jan-20 | € 1,510,500.00 |
| Revenues | Sep | 2021 | Q1 | 30-Sep-20 | Jan _ forecast | 13-Jan-20 | € 1,590,000.00 |
| Revenues | Jul | 2021 | Q1 | 31-Jul-20 | Feb _ forecast | 10-Feb-20 | € 1,545,000.00 |
| Revenues | Aug | 2021 | Q1 | 31-Aug-20 | Feb _ forecast | 10-Feb-20 | € 1,515,000.00 |
| Revenues | Sep | 2021 | Q1 | 30-Sep-20 | Feb _ forecast | 10-Feb-20 | € 1,590,000.00 |
This is the forecast date table (not the same as the calendar table, which is linked to the sales date):
| Forecast Date | Forecast Name | Index |
| 9-Dec-19 | Dec _ forecast | 1 |
| 13-Jan-20 | Jan _ forecast | 2 |
| 10-Feb-20 | Feb _ forecast | 3 |
| 9-Mar-20 | Mar _ forecast | 4 |
| 13-Apr-20 | Apr _ forecast | 5 |
| 11-May-20 | May _ forecast | 6 |
| 8-Jun-20 | Jun _ forecast | 7 |
| 13-Jul-20 | Jul _ forecast | 8 |
| 10-Aug-20 | Aug _ forecast | 9 |
| 14-Sep-20 | Sep _ forecast | 10 |
| 12-Oct-20 | Oct _ forecast | 11 |
| 9-Nov-20 | Nov _ forecast | 12 |
And what I would like to do is explain the following:
Q1 results based on Dec_Forecast is 4.672M
Q1 results based on Jan_Forecast is 4.623M (there is a -49.5K variance from Jan's forecast and Dec's forecast)
Q1 results based on Feb_Forecast is 4.650M (there is a +27K variance from Feb's forecast and Jan's forecast)
I would like that the measures that calculate the sum of each months' forecasted revenues have a forecast date. And instead of having the forecast date in a column in the sales table, I would like to have it on a separate table, which I can update on the fly in the editor (if needed).
Super UserHi,
Could you also show me your expected result in another table? Thank you.