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.
I am trying to create a forecast value but when applying this query it is showing some error
let
Source = Source,
AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), // Add an index column
AddMovingAverage = Table.AddColumn(AddIndex, "MovingAverage", each
let
currentIndex = [Index],
previousThreeValues = List.FirstN(List.Reverse(Table.Column(AddIndex, "SalesValue")), currentIndex),
avg = List.Average(previousThreeValues)
in
if currentIndex > 3 then avg else null
),
RemoveIndex = Table.RemoveColumns(AddMovingAverage, {"Index"}), // Remove the index column
AddForecastedValue = Table.AddColumn(RemoveIndex, "ForecastedValue", each
let
currentDate = [Date],
nextDateRow = Table.SelectRows(RemoveIndex, each [Date] = currentDate + #duration(1, 0, 0, 0)),
nextDateValue = if Table.RowCount(nextDateRow) > 0 then nextDateRow{0}[SalesValue] else null,
movingAverage = [MovingAverage]
in
if nextDateValue <> null and movingAverage <> null then nextDateValue + movingAverage else null
),
RemoveMovingAverage = Table.RemoveColumns(AddForecastedValue, {"MovingAverage"}) // Remove the intermediate MovingAverage column
in
RemoveMovingAverage
Solved! Go to Solution.
You seem to be working with nested Tables. You have at least two each within your Table.AddColumn. I recommend using (x) => x[Date] instead of each [Date] for your nested table. Now x[Date] will refer to your nested date and [Date] will refer to your outer date (if it exists).
You seem to be working with nested Tables. You have at least two each within your Table.AddColumn. I recommend using (x) => x[Date] instead of each [Date] for your nested table. Now x[Date] will refer to your nested date and [Date] will refer to your outer date (if it exists).
Ohk
Hello Rohit001,
I'm not sure if this will be enough to solve your problem but it should be a step in the right direction. I notice your "Date" column is of type text. The query would therefore trip up on the step,
currentDate + #duration(1, 0, 0, 0))
If you change the column to type date before your calculation you may get closer to a solution.
Hope this helps.
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.