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 the period from 15/01/2024 to 17/01/2025.
How can I calculate my monthly revenue prorated for the first and last months, and evenly distributed across the other months in between?
I expect to have 54.39€ in January 2024, 99.20€ per month from February 2024 to December 2024, and 54.39€ in January 2025.
I don't know how to calculate this with DAX or model it in Power BI.
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"
7 Replies
- lbendlin
Super User
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"- lbendlin
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?