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

     

v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity 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
    Icon for Administrator rankAdministrator
    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