Forum Discussion
How to create a Dynamic 15 Months Forecast
Hi, I'm trying to create a 15 months forecast with the sample data below for each client on a monthly basis, I'll also like to apply a reduction factor (percentage) to the forecast result 12 months into the forecast based on a selected value (I'll create a slicer for this based on a factor table I'll create).
To create some context, the data for each Client is generated on a monthly basis (granularity) so its fine for the forcast to be monthly too, the assumption is that the Data table has data up to the maximum Current year month, what I'll like to do is show a forecast based on past data (assuming there is 3 years worth of data but forcast can be based on last 12 months), I can't use LINESTX because I'm stuck with an old Power Bi version so I need to come up with other solution either using DAX measures, or creating the forcast in power wuqry or combine both.
I have attached a sample table.
| Date (Month) | Client | Revenue | Cost | Forecasted Revenue | Forecasted Cost |
| 01-Jan-24 | A | 57692.01 | 40384.41 | ||
| 01-Jan-24 | B | 58804.32 | 41163.02 | ||
| 01-Jan-24 | C | 13008.06 | 9105.64 | ||
| 01-Feb-24 | A | 17022.69 | 11915.88 | ||
| 01-Feb-24 | B | 36181.77 | 25327.24 | ||
| 01-Feb-24 | C | 55339.18 | 38737.43 | ||
| 01-Mar-24 | A | 51527.67 | 36069.37 | ||
| 01-Mar-24 | B | 59660.20 | 41762.14 | ||
| 01-Mar-24 | C | 48252.35 | 33776.65 | ||
| 01-Apr-24 | A | 42848.98 | 29994.29 | ||
| 01-Apr-24 | B | 45355.01 | 31748.51 | ||
| 01-Apr-24 | C | 77968.38 | 54577.87 | ||
| 01-May-24 | A | 58854.28 | 41198.00 | ||
| 01-May-24 | B | 2204.65 | 1543.26 | ||
| 01-May-24 | C | 24024.60 | 16817.22 | ||
| 01-Jun-24 | A | 15454.14 | 10817.90 | ||
| 01-Jun-24 | B | 39337.20 | 27536.04 | ||
| 01-Jun-24 | C | 31359.52 | 21951.66 | ||
| 01-Jul-24 | A | 22033.10 | 15423.17 | ||
| 01-Jul-24 | B | 60219.68 | 42153.78 | ||
| 01-Jul-24 | C | 2532.68 | 1772.88 | ||
| 01-Aug-24 | A | 1332.11 | 932.48 | ||
| 01-Aug-24 | B | 73139.49 | 51197.64 | ||
| 01-Aug-24 | C | 4187.82 | 2931.47 | ||
| 01-Sep-24 | A | 9166.60 | 6416.62 | ||
| 01-Sep-24 | B | 14545.00 | 10181.50 | ||
| 01-Sep-24 | C | 6197.65 | 4338.36 | ||
| 01-Oct-24 | A | 472.90 | 331.03 | ||
| 01-Oct-24 | B | 216.47 | 151.53 | ||
| 01-Oct-24 | C | 8.33 | 5.83 | ||
| 01-Nov-24 | A | 5849.64 | 4094.75 | ||
| 01-Nov-24 | B | 4695.69 | 3286.98 | ||
| 01-Nov-24 | C | 3761.55 | 2633.09 | ||
| 01-Dec-24 | A | 60576.61 | 42403.63 | ||
| 01-Dec-24 | B | 61744.54 | 43221.18 | ||
| 01-Dec-24 | C | 13658.46 | 9560.92 | ||
| 01-Jan-25 | A | 17873.82 | 12511.68 | ||
| 01-Jan-25 | B | 37990.86 | 26593.60 | ||
| 01-Jan-25 | C | 58106.14 | 40674.30 | ||
| 01-Feb-25 | A | 54104.05 | 37872.84 | ||
| 01-Feb-25 | B | 62643.21 | 43850.25 | ||
| 01-Feb-25 | C | 50664.97 | 35465.48 | ||
| 01-Mar-25 | A | 44991.43 | 31494.00 | ||
| 01-Mar-25 | B | 47622.76 | 33335.93 | ||
| 01-Mar-25 | C | 81866.80 | 57306.76 | ||
| 01-Apr-25 | A | 61796.99 | 43257.90 | ||
| 01-Apr-25 | B | 2314.88 | 1620.42 | ||
| 01-Apr-25 | C | 25225.83 | 17658.08 | ||
| 01-May-25 | A | 16226.85 | 11358.79 | ||
| 01-May-25 | B | 41304.06 | 28912.84 | ||
| 01-May-25 | C | 32927.50 | 23049.25 | ||
| 01-Jun-25 | A | 23134.76 | 16194.33 | ||
| 01-Jun-25 | B | 63230.66 | 44261.46 | ||
| 01-Jun-25 | C | 2659.31 | 1861.52 | ||
| 01-Jul-25 | A | 1398.72 | 979.10 | ||
| 01-Jul-25 | B | 76796.46 | 53757.53 | ||
| 01-Jul-25 | C | 4397.21 | 3078.05 | ||
| 01-Aug-25 | A | 9624.93 | 6737.45 | ||
| 01-Aug-25 | B | 15272.25 | 10690.58 | ||
| 01-Aug-25 | C | 6507.53 | 4555.27 | ||
| 01-Sep-25 | A | 496.55 | 347.58 | ||
| 01-Sep-25 | B | 227.29 | 159.11 | ||
| 01-Sep-25 | C | 8.75 | 6.12 | ||
| 01-Oct-25 | A | 54518.95 | 38163.26 | ||
| 01-Oct-25 | B | 55570.08 | 38899.06 | ||
| 01-Oct-25 | C | 12292.62 | 8604.83 | ||
| 01-Nov-25 | A | 16086.44 | 11260.51 | ||
| 01-Nov-25 | B | 34191.77 | 23934.24 | ||
| 01-Nov-25 | C | 52295.53 | 36606.87 | ||
| 01-Dec-25 | A | 48693.65 | 34085.55 | ||
| 01-Dec-25 | B | 56378.89 | 39465.22 | ||
| 01-Dec-25 | C | 45598.47 | 31918.93 | ||
| 01-Jan-26 | A | 40492.29 | 28344.60 | ||
| 01-Jan-26 | B | 42860.48 | 30002.34 | ||
| 01-Jan-26 | C | 73680.12 | 51576.08 | ||
| 01-Feb-26 | A | 55617.29 | 38932.11 | ||
| 01-Feb-26 | B | 2083.39 | 1458.38 | ||
| 01-Feb-26 | C | 22703.25 | 15892.27 |
Thanks.
Hello Tobz007,
Can you please try the following:
Dynamic 15Month Forecast Revenue = VAR CurrentDate = MAX('DataTable'[Date]) VAR Last12MonthsAvgRevenue = CALCULATE( AVERAGE('DataTable'[Revenue]), DATESINPERIOD('DataTable'[Date], CurrentDate, -12, MONTH) ) VAR ReductionFactor = SELECTEDVALUE('Factor Table'[Factor], 1) VAR MonthDiff = DATEDIFF(CurrentDate, TODAY(), MONTH) VAR BaseForecastRevenue = IF( ISBLANK('DataTable'[Revenue]), Last12MonthsAvgRevenue, 'DataTable'[Revenue] ) RETURN IF( ISBLANK('DataTable'[Revenue]), IF( MonthDiff > 12, BaseForecastRevenue * ReductionFactor, BaseForecastRevenue ), 'DataTable'[Revenue] )
3 Replies
Hello Tobz007,
Can you please try the following:
Dynamic 15Month Forecast Revenue = VAR CurrentDate = MAX('DataTable'[Date]) VAR Last12MonthsAvgRevenue = CALCULATE( AVERAGE('DataTable'[Revenue]), DATESINPERIOD('DataTable'[Date], CurrentDate, -12, MONTH) ) VAR ReductionFactor = SELECTEDVALUE('Factor Table'[Factor], 1) VAR MonthDiff = DATEDIFF(CurrentDate, TODAY(), MONTH) VAR BaseForecastRevenue = IF( ISBLANK('DataTable'[Revenue]), Last12MonthsAvgRevenue, 'DataTable'[Revenue] ) RETURN IF( ISBLANK('DataTable'[Revenue]), IF( MonthDiff > 12, BaseForecastRevenue * ReductionFactor, BaseForecastRevenue ), 'DataTable'[Revenue] )Thanks for this Sahir_Maharaj I made some modifications but your response was a great pointer.
Hi, Tobz007
May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
