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 Cali_2020,
So you mean 'Forecasted Amount' is a measure formula that summarizes other table records? If this is a case, you can use measure formula in iterator functions to summarize function expression fields.
BTW, current power bi does not support to create a dynamic calculated column/table based on filter or slicer. If you measure formula are dynamic based on filter, its result will be fixed in the calculated table formula.
Regards,
Xiaoxin Sheng
Hi,
Could you also show me your expected result in another table? Thank you.