forecasting scenarios based on initial numbers

b24569314 · ‎05-25-2023

I need to create a forecast based on a set of initial numbers that will be pulled auotmatically from a data source.

this is what my initial data looks like

Year (current	Total Products	Green Products
2023	1000	200

is then want to be able to apply a formula to total producs and green product to create a forecast based on some input criteria, text in green would be the forecasted values

	A	B	C
1	Year	Total Products	Green Products
2	2023	1000	200
3	2024	1010	220
4	2025	1020	240
5	etc	etc	etc

The formula for the forcast are as follows

Total Products = previous year Total Product * growth factor

Green Products = previous year Green products - previous year Green product * green products discontinued factor + sum(Total Products - (total products/(1+growth factor)), previous month Green product * green products discontinued factor), (total products - previous year green products)* other product discontinued factor) * new green products factor

Growth factor is a percentage that needs to be adjustable between 1 and 10%

Green products discontinued factor is a percentage that needs to be adjusted between 1 and 10 %

Other products discontinued factor is a percentage that needs to be adjusted between 1 and 10 %

New Green products added factor is a percentage that needs to be adjusted between 10 and 50 %

Currently using parameters for the above.

figured out how to generate a list for the months including the initial value by using something like the below

let
    StartYear = 2023,
    EndYear = 2035,

    YearList = List.Generate(
        () => StartYear,
        each _ <= EndYear,
        each _ + 1
    ),

    CustomList = List.Generate(
        () => [Year = StartYear, Value = 1000],
        each [Year] <= EndYear,
        each [Year = [Year] + 1, Value = [Value] * 1.01],
        each [Value]
    ),

    NewList = List.Generate(
        () => [Year = StartYear, Value = 200],
        each [Year] <= EndYear,
        each [Year = [Year] + 1, Value = [Value] + ([Value] * 0.07)],
        each [Value]
    ),

    Result = Table.FromColumns({YearList, CustomList, NewList}, {"Year", "Total Products", "Green Products"}),

Challanges are:

- inputs are not dynamic

- can't figure out the formula

Tried creating a manual data table with the years and initial data then use a measure like below to forecast the Products increase

Total Producst Forecast = 
var a = calculate (LASTNONBLANKVALUE('Table'[Total Products],calculate(sum('Table'[Total Products]))), all ('Table'))
return 
a * (1 + 'Growth Factor'[Growth Factor Value])

However that only works for the first row, subsequent rows have the same data

I know i can use something like

Previous row value = var a=CALCULATE(SUM(Table1[Index]))-1
return CALCULATE(MAX('Table1'[Value]),FILTER(ALL('Table1'),'Table1'[Index]=a))

to get the previous row in a new column which could be helpfull with some of the calculations.

I think i got some of the pieces of the puzzle together but I think i'm still missing something.

v-yanjiang-msft · ‎05-31-2023

Hi @b24569314 ,

I create a sample to illustrate how parameter can be used in your problem.

1. Create a New parameter>Numeric range.

Set it like below:

Then a slicer will be automatically created, we can change value in the slicer as we need.

2. Create a measure.

Total Products =
MAX ( 'Table'[Total Products] )
    * POWER (
        [Growth factor],
        MAX ( 'Forcast'[Year] ) - MAX ( 'Table'[Year (current)] )
    )

Get the correct result:

I havn't created the Green Products measure because I'm not quite clear about the logic, the sections in the red line box are separated from the preceding parts by commas and seem to have little to do with each other.

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

b24569314 · ‎05-31-2023

Thanks that makes sense and its really helpfull, could you take a stab at the green products measure i might have missed some brakets/commas above but the logic is basically this:

Number of Green Products left after discontinued factor from prior year

+

New Green Products factor

*

(Number of new products + number of discontinued green products from prior year + Number of discontinued other products from prior year)

Thanks