Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
@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 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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |