Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
alexsandro
Frequent Visitor

replicate spreadsheet calculation

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

alexsandro_0-1651406630189.png

 

link of spreadsheet below:

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

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

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.