Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 b...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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)*_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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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"

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Anonymous 

     

    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.