replicate spreadsheet calculation

[alexsandro]

I'm trying to publish this formula of the spreadsheet in the power bi, but without success.

link of spreadsheet below:

https://docs.google.com/spreadsheets/d/11BhRKjWPtPdeFuFvZSlBm0mesjtpGsWM/edit?usp=sharing&ouid=10262... 

[Greg_Deckler]

@alexsandro You need to be able to return the previous row like in MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[alexsandro]

unfortunately it didn't work as I need to iterate over the same previous calculation and not over the previous price

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddFJDoMwDAXQu7COhOfhLIht73+EEtNGJdAFKHJ4+Y7ZtgWXVs8LoK9oJV4JqJd6gc4yYEtN+l3ubVvogg9I3VaV9HjriaXVeQjU7NgO9dI8ax7aor6fOLfemScWl5nHl5NitX7TbvrJ1gkzDmz+0Dk3IfbwKG0XLevAjB3zHSe6FfWJ6qBSPd2DHVIgtHRMmgY3tLr5+F02RiYcXDr/ajd7HjgTKJzZCH95VPhDtmAq57Lvbw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [code_operation = _t, branch = _t, code = _t, date = _t, #"amount day" = _t, Amount = _t, price = _t, #"replicate this formula" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"amount day", Int64.Type}, {"Amount", Int64.Type}, {"code_operation", Int64.Type}, {"date", type date}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"price", type number}}, "de-DE"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"code_operation", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 then [price] else List.Accumulate({1..[Index]},#"Added Index"{0}[price],(state,current)=>(#"Added Index"{current}[amount day]*state+#"Added Index"{current}[Amount]*#"Added Index"{current}[price])/(#"Added Index"{current}[amount day]+#"Added Index"{current}[Amount])))
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[alexsandro]

IT WAS PERFECT, THANK YOU SO MUCH...
now I'm going to try to replicate this for several codes
you are very good at it, super professional

[alexsandro]

thanks Greg, I'll see.