Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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)))
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.
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.
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 ! 🙂
@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)))
Thank you so much for your valuable solution 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |