Separate date tables?

[Cali_2020]

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!!!

[Ashish_Mathur]

Hi,

Please share a simle sample datasets(s) and show the expected result.

[Cali_2020]

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).

[Ashish_Mathur]

Hi,

Could you also show me your expected result in another table?  Thank you.

[v-shex-msft]

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

[Cali_2020]

Hi @v-shex-msft ,

Thanks for your reply but how can you use this formula with variables?

Thanks again!

[v-shex-msft]

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

[amitchandak]

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.