Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Revenue Recognition

Hi, currently I am doing my revenue recognition on Excel and i want to change and go on power BI.   For example, I have an invoice of 1200€ dated 15/01/2024. This revenue must be recognized over th...
Need Help
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    1 year ago

    That way you are missing Jan 15 2024.

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyMFDSUTLUNzTVNzIwMoGwzUFsU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Amount = _t, #"Invoice Date" = _t, #"Recognition End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice Date", type date}, {"Recognition End Date", type date}, {"Amount", Currency.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each {Int64.From([Invoice Date])..Int64.From([Recognition End Date])}),
    #"Expanded Range " = Table.ExpandListColumn(#"Added Custom", "Range"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Range ",{{"Range", type date}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Month", each #date(Date.Year([Range]),Date.Month([Range]),1), type date),
    #"Grouped Rows" = Table.Group(#"Added Custom2", {"Month"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "weight", each [Count]/Number.From(Date.EndOfMonth([Month])-Date.StartOfMonth([Month])+#duration(1,0,0,0)),type number),
    #"Added Custom3" = Table.AddColumn(#"Added Custom1", "Value", each [weight]/List.Sum(#"Added Custom1"[weight])*#"Changed Type"[Amount]{0},Currency.Type)
in
    #"Added Custom3"

     

lbendlin's avatar
lbendlin
Super User
1 year ago

Are you distributing/amortizing by calendar days, by business days or by month fractions?

 

here is an example using calendar days.

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyMFDSUTLUNzTVNzIwMoGwzUFsU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Amount = _t, #"Invoice Date" = _t, #"Recognition End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice Date", type date}, {"Recognition End Date", type date}, {"Amount", Currency.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each {Int64.From([Invoice Date])..Int64.From([Recognition End Date])}),
    #"Expanded Range " = Table.ExpandListColumn(#"Added Custom", "Range"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Range ", "Custom", each [Amount]/Table.RowCount(#"Expanded Range "), Currency.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Range", type date}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Month", each #date(Date.Year([Range]),Date.Month([Range]),1), type date),
    #"Grouped Rows" = Table.Group(#"Added Custom2", {"Month"}, {{"Amortized", each List.Sum([Custom]), type number}})
in
    #"Grouped Rows"

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thank you for this answer.

 

However, i want the revenue to be prorated on first and last month. But in between i want the revenue to be even every month in between. Does not matter if the month has 31 or 30 days, the revenue must be the same

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    What is your rule for prorating the first and last month?  Based on the number of calendar days in that month? Number of business days in that month?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Number of days in the month.

      For the month of January 24, it would be the remaining days of the month so (31-15)/31=0,52.

      And for the month of January 25 it would be 17/31=0,55.

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        That way you are missing Jan 15 2024.

         

         

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyMFDSUTLUNzTVNzIwMoGwzUFsU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Amount = _t, #"Invoice Date" = _t, #"Recognition End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice Date", type date}, {"Recognition End Date", type date}, {"Amount", Currency.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each {Int64.From([Invoice Date])..Int64.From([Recognition End Date])}),
    #"Expanded Range " = Table.ExpandListColumn(#"Added Custom", "Range"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Range ",{{"Range", type date}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Month", each #date(Date.Year([Range]),Date.Month([Range]),1), type date),
    #"Grouped Rows" = Table.Group(#"Added Custom2", {"Month"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "weight", each [Count]/Number.From(Date.EndOfMonth([Month])-Date.StartOfMonth([Month])+#duration(1,0,0,0)),type number),
    #"Added Custom3" = Table.AddColumn(#"Added Custom1", "Value", each [weight]/List.Sum(#"Added Custom1"[weight])*#"Changed Type"[Amount]{0},Currency.Type)
in
    #"Added Custom3"