Rolling Forecast - Reduce Calculation Speed of the Measure

[George1973]

Hi All,
I have a rolling forecats of 12 Months ahead based on past months statistical analysis. It does not matter now how it's calculated, it's beeing calculated perfecctly, in terms of result itself.
But the problem is the measure speed itself.

So, I have the following challanges (here is the sample data):

As you can understand, the gray area is the past months performances, CM is the "Current Month" and the "Green Area" is the forecast for upcomming 6 monts. +1M, +2M.. etc.. are the the first, second, etc.. future months respectively

Well, there is no problem to deal with the grey area numbers, because they are "phisicaly" written somewhere in the DB and summurising them take less than seconds.

The problem is with the forecasting numbers, for each months.. because they are "Variables" written only in Operating Memory". So, when it comes to calculate those numbers for more than 5000 items (Product) I have difficulties with timing:
- Even when I split the forecast into indipendent +1M, +2M.. etc.. measures, each measure takes 20-30 seconds to be calculated.

So, I wonder I there is an approach, somehow to "Store" those forecasting months and measures into the "Temporary - Changing" table to get them phisicaly written into the Power BI DB, or there is another solution the boost the speed of the measures?

Thanks in advance,

[johnt75]

If you can't precalculate the table then I'm not sure how you can speed it up I'm afraid.

As far as the slicers go, you could create a new Date table which has no relationship to any other table, and use that as the slicer. Then in your measures you can use SELECTEDVALUE('Disconnected Date'[Year Month]) as the basis for your calculations.

[johnt75]

If you don't need the values to be affected by filters or slicers you could create a summary table which would only be calculated once during data refresh, then pull the values for your visuals from there. Something along the lines of

Summary Table = SUMMARIZECOLUMNS( 'Products'[Product], "+1M", [+1M], "+2M", [+2M] )

[George1973]

Dear @johnt75 ,

Thanks a lot for the response,

Your suggestion sounds interesting. But the problem is the filtering: So, I have a single selecting Month-Year and based on the selection, The -[-3M],[-2M],[-1M],[CM],[+1M],[+2M[,[+3M] etc.. data is beeing calculated. I have another filters-Slicers (King of Configuration Set) needed for the forecast.

Based on above mentioned I need some kind of "Dinamic" table rolling back and forward based on the selected single month. As I have mentioned before, I'm getting the final result (I have created a DAX code with more than 1500 record lines), but the problem is the timing..

Ok. If we can not do it with table, I have then another question:
In my DAX code, I have forecast for the each upcomming 12 months - Seperately for each month (12 independent Variables).
I have a single month selection slicer and I want to apply those 12 variable to the next 12 month upfront from the selected month. The question - the challange is:
How to overcome the "One month selection" filter with measures, or filters on specific visual?  
Hint: I have tried to apply a "Global" filter (Next to the Visual panel) with dates (Relative Date - Next 12 month - I had indexed each months with an uniqe code) like that:

VAR MnDiff= maxx(datekey,datekey[MonthIndex])- selectedvalue(datekey[MonthIndex])

VAR FutRes=
SWITCH(true(),
MnDiff=0, Forcast0,
MnDiff=1, Forcast1,
MnDiff=2, Forcast2,
MnDiff=3, Forcast3,
MnDiff=4, Forcast4,
MnDiff=5, Forcast5,
MnDiff=6, Forcast6,
MnDiff=7, Forcast7,
MnDiff=8, Forcast8,
MnDiff=9, Forcast9,
MnDiff=10, Forcast10,
MnDiff=11, Forcast11,

Blank())
Return
FutRes

Then, I disconnect the visual from "Single Month Selection" visual through "Format->Edit Interactions" and apply the "Global Filter" with relative date +12 month..

And finaly I create a matrix visual, putting Product codes in rows and month indexes in columns.. BUT.. 🙂
I'm getting not complet picture.. Please see below:

Here the probem is:
1. The month indexed in the columns are not dependent on the slicer selection - They begin from tye current month (April). But I want they to be bound to slicer
2. The whole time needed to get the result is more than 2 min.. and it is not acceptable 🙂

So, As you can see I'm in the real trouble.

I would be really geratful if you could help me with these dilemas 🙂