Forum Discussion
Get estimated value for future months
I have a table with sales value and months. I want to estimate the sales values for future months based on previous months sales. So if august is the last month with data i want septembers value to be the value for august + avg sales for previous months.
So with the numbers below septembers estimated value should be 90. For October it should be 100 and so on.
Hi, Anonymous
You can try the following methods.
Utfall ack = Var _Sum=CALCULATE(SUM('Table'[Utfall]),FILTER(ALL('Table'),[Month Number]<=MAX('Table'[Month Number]))) RETURN IF(SELECTEDVALUE('Table'[Utfall])=BLANK(),BLANK(),_Sum)Estimated = Var _maxmonth=CALCULATE(MAX('Table'[Month Number]),FILTER(ALL('Table'),[Utfall]<>BLANK())) Var _avg=DIVIDE(CALCULATE(SUM('Table'[Utfall]),ALL('Table')),_maxmonth) Var _previous=CALCULATE([Utfall ack],FILTER(ALL('Table'),[Month Number]=_maxmonth)) RETURN IF(SELECTEDVALUE('Table'[Utfall])=BLANK(),_previous+(MAX('Table'[Month Number])-_maxmonth)*_avg,BLANK())Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi Anonymous - Ensure that your table includes all months of the year, with missing months having blank or zero values for the sales column.
Create a measure as below
Avg_Sales = AVERAGEX(FILTER('Table', NOT(ISBLANK('Table'[Utfall]))), 'Table'[Utfall])
another measure for last moth:
Last_Month_Sales = MAXX(FILTER('Table', NOT(ISBLANK('Table'[Utfall]))), 'Table'[Utfall Ack])
Now lets add new column in table to estimate sales values for future months
Estimated_Sales =
IF(
ISBLANK('Table'[Utfall]),
VAR LastMonthSales = MAXX(FILTER('Table', NOT(ISBLANK('Table'[Utfall]))), 'Table'[Utfall Ack])
VAR AvgSales = AVERAGEX(FILTER('Table', NOT(ISBLANK('Table'[Utfall]))), 'Table'[Utfall])
RETURN LastMonthSales + (ROW_NUMBER() - <row offset>) * AvgSales,
'Table'[Utfall]
)Hope this works and helps you.
Hi, Anonymous
You can try the following methods.
Measure = Var _maxmonth=CALCULATE(MAX('Table'[Month num]),FILTER(ALL('Table'),[Utfall]<>BLANK())) Var _avg=DIVIDE(CALCULATE(SUM('Table'[Utfall]),ALL('Table')),_maxmonth) Var _previous=CALCULATE(SUM('Table'[Utfall ack]),FILTER(ALL('Table'),[Month num]=_maxmonth)) RETURN _previous+(MAX('Table'[Month num])-_maxmonth)*_avgIs this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! I tried your measure but it did not quite work. I used the month column from the table instead of the regular date table. It seems like the estimated shows correct from month 5, but it adds the avg to the 'Utfall' instead of 'utfall ack'
"
test data uppräknat utfall =Var _maxmonth=CALCULATE(MAX('Test data'[Månad nummer]),FILTER(ALL('Test data'),[test data Utfall] <>BLANK() ))Var _avg=DIVIDE(CALCULATE(SUM('Test data'[test data Utfall]),ALL('Test data')),_maxmonth)Var _previous=CALCULATE(SUMx('Test data', [test data Utfall Ack]),FILTER(ALL('Test data'),[Månad nummer]=_maxmonth))RETURN_previous+(MAX('Datum'[Month Number])-_maxmonth)*_avg"
