Forum Discussion
Separate date tables?
Hello! I need some help regarding current and previous period dax expressions.
In our organization, we analyze variances from the most recent forecast to the previous forecast, in which all is contained in a sales table. Therefore, there are two types of time dimensions:
1) sales date, in which the sales have happened or expected to happen and is divided by fiscal periods
2) the forecasted date, which is the date the forecast is performed
I have a calendar table and this is linked to the sales date column in the fact table. As our forecast dates in the future could possible change from one day to another, I would prefer not to have a column in the fact table with the forecast date. I think it’s best to manage this separately by creating a separate table aside from a calendar table in which I can link to the sales table by an ID on both tables (e.g., “Jan _ forecast”). The following expression to calculate revenues returns blank. I have tried using “filter” as well as “related” functions but nothing seems to work and I can’t wrap my head around why.
VAR current_forecast = CALCULATE(MAX('Forecast_Date'[Forecast_Date_Table]),'Forecast
Return
Calculate([FY20_ForecastedRev],FILTER(RELATEDTABLE('Forecast_Date_Table'), 'Forecast_Date_Table'[Forecast_Date] = current_forecast))
Moreover, the cardinality on the tables seems to be fine. Does it make sense to have two separate tables? I have been looking for a solution to review a forecast over forecast scenario but I haven’t found anything on the forum that has helped so far so if anyone can point me in the right direction or has any suggestions or advice, I’d be greatly appreciative!!!
7 Replies
I prefer to have a single date dimension. Unless requirement forces it. The Advantage is that one can put data together. So Common dimensions allow seeing data together. Sales and Forecast fact should be separate, not all filter of sales will be used for forecasting.
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
