Forum Discussion
Separate date tables?
Hi,
Please share a simle sample datasets(s) and show the expected result.
Hi 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).
HI Cali_2020,
You can use following calculate table formula to extract and summarize records from the sample data table:
Forecaste = SUMMARIZE('Table',[Forecast Date],[Forecast Name],"Forecasted Amount",SUM('Table'[Forecasted Amount]))Regards,
Xiaoxin Sheng
Hi Anonymous ,
Thanks for your reply but how can you use this formula with variables?
Thanks again!
Hi,
Could you also show me your expected result in another table? Thank you.
