Forum Discussion

bryanrendra's avatar
bryanrendra
Icon for Helper II rankHelper II
6 years ago
Solved

Forecasting 2 years ahead for Multiple items

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.

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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's avatar
    Icey
    6 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      bryanrendra
      Icon for Helper II rankHelper II
      6 years ago

      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 ! 🙂