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"