topic Re: replicate spreadsheet calculation in Developer

replicate spreadsheet calculation

alexsandro — Mon, 02 May 2022 11:46:37 GMT

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=102623328097540554419&rtpof=true&sd=true

Re: replicate spreadsheet calculation

Greg_Deckler — Mon, 02 May 2022 12:19:58 GMT

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

Re: replicate spreadsheet calculation

alexsandro — Mon, 02 May 2022 12:25:16 GMT

thanks Greg, I'll see.

Re: replicate spreadsheet calculation

alexsandro — Wed, 04 May 2022 02:18:29 GMT

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

Re: replicate spreadsheet calculation

lbendlin — Wed, 04 May 2022 19:50:23 GMT

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

Re: replicate spreadsheet calculation

alexsandro — Thu, 05 May 2022 13:07:40 GMT

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