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'm trying to publish this formula of the spreadsheet in the power bi, but without success.
link of spreadsheet below:
Solved! Go to 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 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
unfortunately it didn't work as I need to iterate over the same previous calculation and not over the previous price
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".
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
thanks Greg, I'll see.
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 |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |