Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cali_2020
Frequent Visitor

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 7
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

 

Thank you for your reply. Below is a sample date set:

 

Date InputFiscal MonthFiscal YearFiscal QuarterEOM Sales DateForecast NameForecast DateForecasted Amount
RevenuesJul2021Q131-Jul-20Dec_ forecast9-Dec-19€         1,575,000.00
RevenuesAug2021Q131-Aug-20Dec_ forecast9-Dec-19€         1,507,500.00
RevenuesSep2021Q130-Sep-20Dec_ forecast9-Dec-19€         1,590,000.00
RevenuesJul2021Q131-Jul-20Jan _ forecast13-Jan-20€         1,522,500.00
RevenuesAug2021Q131-Aug-20Jan _ forecast13-Jan-20€           1,510,500.00
RevenuesSep2021Q130-Sep-20Jan _ forecast13-Jan-20€         1,590,000.00
RevenuesJul2021Q131-Jul-20Feb _ forecast10-Feb-20€         1,545,000.00
RevenuesAug2021Q131-Aug-20Feb _ forecast10-Feb-20€          1,515,000.00
RevenuesSep2021Q130-Sep-20Feb _ forecast10-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 DateForecast NameIndex
9-Dec-19Dec _ forecast1
13-Jan-20

Jan _ forecast

2
10-Feb-20Feb _ forecast3
9-Mar-20Mar _ forecast4
13-Apr-20Apr _ forecast5
11-May-20May _ forecast6
8-Jun-20Jun _ forecast7
13-Jul-20Jul _ forecast8
10-Aug-20Aug _ forecast9
14-Sep-20Sep _ forecast10
12-Oct-20Oct _ forecast11
9-Nov-20Nov _ forecast12

 

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,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft ,

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.