Forecasting 2 years ahead for Multiple items

[bryanrendra]

I have a table with multiple items. each items has different prices every year. I just want to make a prediction or forecasting for the next 2 years based on the 2020 price and ignore the item that doesnt have price in 2020 "sold out"

Here is the table:

 

My desired result:

the forecast calculation is Price 2020 * 110% 

 My ultimate goal is creating a matrix table:

 I am not sure if i have to create a measure or new table or new column. I am really need your help, thank you so much.

[amitchandak · Accepted solution]

@bryanrendra , Try like

meausre =
var _max = year(today)
return
calculate(if(max(Year[Year]) >=_max, Average(Table[Price]) ,Averagex(filter(all(Year),Year[Year]=_max),Table[Price])*power(1.1,Max(Year[Year])-max)))

[Icey · Accepted solution]

Hi @bryanrendra ,

Please check:

1. Enter data to create a Year table. 

2. Create a measure like so:

Meausre 2 = 
VAR t1 =
    ADDCOLUMNS (
        'Table',
        "MaxYear", CALCULATE (
            MAX ( 'Table'[Year] ),
            FILTER (
                'Table',
                'Table'[Book] = EARLIER ( 'Table'[Book] )
                    && 'Table'[Genre] = EARLIER ( 'Table'[Genre] )
            )
        )
    )
VAR t2 =
    FILTER ( t1, [MaxYear] = 2020 && [Year] = 2020 )
VAR t3 =
    CROSSJOIN ( SUMMARIZE ( t2, [Book], [Genre], [Price] ), { 2021, 2022 } )
VAR t4 =
    ADDCOLUMNS ( t3, "price_", [Price] * POWER ( 1.1, [Value] - 2020 ) )
VAR t5 =
    UNION ( 'Table', SUMMARIZE ( t4, [Book], [Genre], [Value], [price_] ) )
RETURN
    IF (
        HASONEVALUE ( 'Year'[Year_] ),
        SUMX ( FILTER ( t5, [Year] = MAX ( 'Year'[Year_] ) ), [Price] ),
        SUMX ( t5, [Price] )
    )

3. Then you will get this:

BTW, .pbix file attached.

Best Regards,

Icey

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

[bryanrendra]

Thank you so much for actually try it and give me different kind of solutions that i never tought before. its a smart idea to break down all of the columns into smaller table. I am really appreciate it ! 🙂

[amitchandak · Accepted solution]

@bryanrendra , Try like

meausre =
var _max = year(today)
return
calculate(if(max(Year[Year]) >=_max, Average(Table[Price]) ,Averagex(filter(all(Year),Year[Year]=_max),Table[Price])*power(1.1,Max(Year[Year])-max)))

[bryanrendra]

Thank you so much for your valuable solution 🙂