Forum Discussion

Ricardo_Moreira's avatar
Ricardo_Moreira
Frequent Visitor
5 years ago
Solved

Calculate periodical amounts based on YTD data

Dear Forum,   I'm quite new to Power BI and would appreciate if someone could help me with a simple challenge or point me out to a solution if there is one already published (I could not find it). ...
Need Help
Tips and Tricks
  • ImkeF's avatar
    ImkeF
    5 years ago

    Hi Ricardo_Moreira ,

    I didn't study your sample data closely, but now I see that you have to apply it on groups of your data (Entity, Category and Year).
    The code looks like this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZNLDoUgEATvwtoFP8lzKeopjPe/xoMxAzj0LNSYSttaHe/b7GYx3npbLqEcey4nZ601zzLBfFS4YnicdI/hedV7kEzciZKJO4MCqTMqkDpXADfuRMmNO5MCqfOnQOqs+ibqPLeiLNFXLxJBmIodei3Cr2JZ7cZd5aMZQscMoWOG0LEbd0XJtqv8GIbQMUPs2I3DomgbdjLMFAtmqvvt08Jw39YjGX3bgF6sbxtbdwb/bFTg17OAX88CCs8DbeOiaBu3exZUeBZ09Pz8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entity = _t, Year = _t, Period = _t, Category = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entity", type text}, {"Year", Int64.Type}, {"Period", Int64.Type}, {"Category", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Entity", "Category", "Year"}, {{"All", each _, type table [Entity=nullable text, Year=nullable number, Period=nullable number, Category=nullable text, Amount=nullable number]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fnPreviousRow", each fnPreviousRow([All], "Amount")),
    #"Expanded fnPreviousRow" = Table.ExpandTableColumn(#"Invoked Custom Function", "fnPreviousRow", {"Period", "Amount", "Previous Row"}, {"Period", "Amount", "Previous Row"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded fnPreviousRow",null,0,Replacer.ReplaceValue,{"Previous Row"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "QtrlyAmount", each [Amount] - [Previous Row], type number)
in
    #"Inserted Subtraction"

     

     

    Please also check the file attached (Query: "Imke").

     

  • Ricardo_Moreira's avatar
    Ricardo_Moreira
    Frequent Visitor
    5 years ago

    Hi ImkeF

    Thank you very much for your reply.

    I was able to follow all the steps mentioned in the link (they were very clear!)  and get the "previous row" in my table but I fail to understand how does it help me to get the QTD values. I have tried on column "Period" and "Amount":

     

     

    The table based on the added column does not give me QTD amounts. Am I missing any additional step?

     

    Thank you again for your support!

    Ricardo

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Hi Ricardo_Moreira ,

      I didn't study your sample data closely, but now I see that you have to apply it on groups of your data (Entity, Category and Year).
      The code looks like this:

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZNLDoUgEATvwtoFP8lzKeopjPe/xoMxAzj0LNSYSttaHe/b7GYx3npbLqEcey4nZ601zzLBfFS4YnicdI/hedV7kEzciZKJO4MCqTMqkDpXADfuRMmNO5MCqfOnQOqs+ibqPLeiLNFXLxJBmIodei3Cr2JZ7cZd5aMZQscMoWOG0LEbd0XJtqv8GIbQMUPs2I3DomgbdjLMFAtmqvvt08Jw39YjGX3bgF6sbxtbdwb/bFTg17OAX88CCs8DbeOiaBu3exZUeBZ09Pz8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entity = _t, Year = _t, Period = _t, Category = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entity", type text}, {"Year", Int64.Type}, {"Period", Int64.Type}, {"Category", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Entity", "Category", "Year"}, {{"All", each _, type table [Entity=nullable text, Year=nullable number, Period=nullable number, Category=nullable text, Amount=nullable number]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fnPreviousRow", each fnPreviousRow([All], "Amount")),
    #"Expanded fnPreviousRow" = Table.ExpandTableColumn(#"Invoked Custom Function", "fnPreviousRow", {"Period", "Amount", "Previous Row"}, {"Period", "Amount", "Previous Row"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded fnPreviousRow",null,0,Replacer.ReplaceValue,{"Previous Row"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "QtrlyAmount", each [Amount] - [Previous Row], type number)
in
    #"Inserted Subtraction"

       

       

      Please also check the file attached (Query: "Imke").

       

      • Ricardo_Moreira's avatar
        Ricardo_Moreira
        Frequent Visitor
        5 years ago

        Hi ImkeF 
        Thank you for your message.
        I could not find the attachment you mention.
        Does this code replace the previous one?

         

        Ricardo