cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## forecasting scenarios based on initial numbers

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.

6 REPLIES 6
Community Support

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.

Frequent Visitor

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

Super User

If you use "What-if?" parameters this will take care of the list generation for you.  From there it is a relatively simple PRODUCTX.

Frequent Visitor

can you expand on that i'm not sure i understand.

Super User
Frequent Visitor

still dont understand how this can be used to create the forecast.

The example in the article linked looks to assume there is data in a column upon which a calculation can be applied with the parameter.

dont understand how this can be applied to generate rows in a table or be used in a measure that bases its calculation on a previous row.

some sample code would really help.

Announcements