Get estimated value for future months

[myboh]

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. 

[v-zhangtin-msft]

Hi, @myboh 

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)*_avg

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.

[myboh]

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"

[v-zhangtin-msft]

Hi, @myboh 

What kind of output do you expect from the test data you just provided? Please show it in Excel.

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.

[myboh]

Okey so i have the data below, with only Utfall for Jan - April

I will then write a measure that sums all sales values for previous month named Utfall ack. For all future months without sales i want to have an estimated sales value based on Utfall ack. So for May i want utfall ack from April = 100 + avg sales Jan-apr = 25, so 125. For June I then want the May value 125 + avg sales Jan-may, 150, and so on. And when the real sales for may comes the utfall ack should show instead of estimated may value

[v-zhangtin-msft · Accepted solution]

Hi, @myboh 

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.

[myboh]

Yes it works now, thank you!!

[rajendraongole1]

Hi @myboh  - 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.