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

 

What is the best way to calculate quarterly periodical data based on a table with quarterly YTD data?

So Q2 QTD=Q2 YTD-Q1 YTD, Q3 QTD=Q3 YTD-Q2 YTD,.......

(see data sample below)

 

Thank you very much in advance, your support is much appreciated.

 

 

 

 

 

 

 

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

     

8 Replies

    • 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
        Community 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").

         

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi Ricardo_Moreira ,

    You can try this query:

    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", "Year", "Period"}, {{"Sum", each List.Sum([Amount]), type nullable number}}),
    #"Grouped Rows1" = 
        Table.Group(
            #"Grouped Rows", {"Entity", "Year"}, 
            {
                {"Data", each 
                    let tab = Table.AddIndexColumn(_,"Index",1,1,Int64.Type) in 
                        Table.AddColumn(
                            tab, "New",
                            (x) => try Table.Max(Table.SelectRows(tab,(y)=>y[Index]=x[Index]-1),"Index")[Sum]
                                    otherwise (x)[Sum]
                        )    
                }
            }
        ),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows1", "Data", {"Period", "Sum", "New"}, {"Data.Period", "Data.Sum", "Data.New"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Result", each [Data.Sum] - [Data.New]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data.Sum", "Data.New"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Data.Period", "Period"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Entity", type text}, {"Year", Int64.Type}, {"Period", Int64.Type}, {"Result", Int64.Type}})
in
    #"Changed Type1"

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Ricardo_Moreira's avatar
      Ricardo_Moreira
      Frequent Visitor
      5 years ago

      Hi Yingjie Li,

      Thank you for your reply.

      I tried your query but I am getting the following error:

      I was not able to open the pbix file because my PBI is running on a different (previous) version than yours.

       

      Thank you for your support.

       

      Regards,

      Ricardo

       

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      5 years ago

      Hi Yingjie Li,

      Thank you for your reply.

      I tried your query but I am getting the following error:

      Ricardo_Moreira_2-1629373258124.pngRicardo_Moreira_3-1629373278710.png

      I was not able to open the pbix file because my PBI is running on a different (previous) version than yours.

       

      Thank you for your support.

       

      Regards,

      Ricardo