Forum Discussion
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...
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"
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?
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.
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"C'est exactmenet ce que je veux avoir comme resultat.
Quelles formules avez vous utilisées ?For each month you compare the participating days to the total number of days. That gives you the weight factor to apply to the total value to find the contribution for each month.