Forum Discussion

Alicetemitope's avatar
Alicetemitope
Frequent Visitor
3 years ago
Solved

Forecasting using previous day value

Hi Team, I hope you all are doing great. I am a newbie to DAX and PBI annd currenntly working on a task to predict cost. I had to calculate the slope(which I did using the Linest function) and add this slope to the preceeding value(where preceeding value = previous day cost). so,

cost prediction for today= actual value of previous day + slope and if there is no actual value present(in the case of future days), then 

cost prediction for future = last calculated cost prediction + slope 

 

so I wrote a dax formula,

cost prediction =
var _PreviousDayCost = CALCULATE([Amortized Cost(€)], PREVIOUSDAY(DIM_Date[Date]))
var _predictedcost = _PreviousDayCost + [Slope]
return
_predictedconsumption
 
evreything works fine until when I don't have any actual and it just returns the slope. is there a way to reference the previously calculated prediction so the right prediction is populated.like in excel where previous cell is referenced. 
 
Thanks in advance for your help and support
Here is a result of my current formulavalue to be added
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Alicetemitope No, but you could do this potentially:

    Measure = 
  VAR __Date = MAX('Table'[Date])
  VAR __Table = 
    ADDCOLUMNS(
      DISTINCT(ALLSELECTED('Table'[Date])),
      "__DailyCost",[Amortized Cost(E)
    )
  VAR __MaxDate = MAXX(FILTER(__Table, [__DailyCost] <> BLANK()), [Date])
  VAR __Value = MAXX(FILTER(__Table, [Date] = __MaxDate), [__DailyCost])
  VAR __Result = __Value * [Slope]
RETURN
  __Result

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Alicetemitope No, but you could do this potentially:

    Measure = 
  VAR __Date = MAX('Table'[Date])
  VAR __Table = 
    ADDCOLUMNS(
      DISTINCT(ALLSELECTED('Table'[Date])),
      "__DailyCost",[Amortized Cost(E)
    )
  VAR __MaxDate = MAXX(FILTER(__Table, [__DailyCost] <> BLANK()), [Date])
  VAR __Value = MAXX(FILTER(__Table, [Date] = __MaxDate), [__DailyCost])
  VAR __Result = __Value * [Slope]
RETURN
  __Result

     

    • Alicetemitope's avatar
      Alicetemitope
      Frequent Visitor
      3 years ago

      Greg_Deckler thank you so much for your help. Your suggestion helped in fine tuning my calculations. In the end, we stopped this approach and decided to use OLS for the predictions. which was much easier and made more sense.