cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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
1 ACCEPTED SOLUTION
Super User

@Alicetemitope No, but you could do this potentially:

``````Measure =
VAR __Date = MAX('Table'[Date])
VAR __Table =
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``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Super User

@Alicetemitope No, but you could do this potentially:

``````Measure =
VAR __Date = MAX('Table'[Date])
VAR __Table =
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``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors