Revenue Recognition

[Donki]

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.

[lbendlin]

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"

[Donki]

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]

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?

[Donki]

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 · Accepted solution]

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"

[Donki]

C'est exactmenet ce que je veux avoir comme resultat.
Quelles formules avez vous utilisées ?

[lbendlin]

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.