SUMX for Rolling Forecast with Two FactTables (Actual x Forecast)

[TheGrilow]

Hi guys,

I have two separeted Fact Tables (Actual and Forecast) linked in a Calendar Dimension Table.  In the Forecast Table I have data of only Month of the "Year to go" (Example: Jun to Dez). Besides, in the Actual table I have only data of the YTD Months (example: Jan to May). So I need to make an weighted average using the two fact tables, filtering the lastdate with data of the Actual in the Forecast Table and interate over this virtual DAX table of Actual and Forecasting.

I know that I could embend the two tables in power query. But as a matter of learning and making the worksheet more smooth. I would like to try this one.

Here is my DataSet 

and my last Dax Measure

my last result, the proble is in the subtotal over the iteration issue of sumx

Thanks in !!!

Data help and tips @facttables fact table and dimension table 

[TheGrilow]

Hi @tamerj1 

The columns are:

FORECASTING TABLE:
[Data Viagem], [Month Name], '[VOLUME], '[DISTANCIA]

ACTUAL TABLE:

'[Data Viagem], [Month Name], [VOLUME], [DISTANCIA]

CALENDARIO TABLE:
[Date], [Month Name]

[tamerj1]

@TheGrilow 

I mean you have a filter on the year and you have the months on the columns of the pivot table. What coulmns are in the rows of the pivot table and from which table?

[TheGrilow]

it is just a column named "year"= 2022 in both tables (fact and Forecasting) its not need in the calculation. The issue is in the subtotal that I need to iterate over the two fact tables!

[tamerj1]

@TheGrilow 

Everything is related to the filter context. In your SUMX variables you are iterating over each table while you should iterate over the table created by the filter contaxt of the pivot table and the filters and then wrap the expression inside SUMX by CALCULATE 

[TheGrilow]

fair enought, but how do I create this table in a Dax Formula? I need to create a table in  month granularity that can be Actual + Forecasting with virtual relationship!

[tamerj1]

@TheGrilow 
First please help me answer these questions 

[TheGrilow]

2022 = Year from Date Table (not needed)
RF06 = Column from Forecasting table (not needed)
Rolling Forecasting = Measure from the image above using both actual and forecasting table fom the conditional of RESULT using the columns listed in the message above.
Forecasting Distance = Measure only from Forecasting table of the image above VAR = Forecasting

[tamerj1]

@TheGrilow 
Please try new measure 

Rolling Forecasting with Correct Total =
SUMX (
    CROSSJOIN (
        SUMMARIZE ( 'Date', 'Date'[Year], 'Date'[Month] ),
        VALUES ( Forecasting[Column] )
    ),
    [Rolling Forecasting]
)

[TheGrilow]

Almost! The only dought is, you´re making a CrossJoin with DateTable and ForecastingTable. Waht about the ActualTable?

Thanks for your help!

[tamerj1]

@TheGrilow 

I don't think the numbers you are getting are accurate. As the forcast table cannot filter the actual table. I would suggest to create a bridge table. 

If this is the only column you are using for slicing the data then you can create a new table using power query containing this column and then build a relationship between this table and the two fact tables then use this column for slicing and in the crossjoin. 

[tamerj1]

Hi @TheGrilow 

what are the columns invloved in the visual and from which tables?