Split revenue per year based to create visual

[nicoenz]

Hi,

i have a table with 3 columns. "Opportunity name", "12_Month_Revenue" and "Start Operating date". The revenues are always the total for 1 year. If an opportunity starts in September of this year it whould add 4 months to this year and 8 months to next. if it starts in July, it should add 6 and 6 respectively. Note that opportunities can start in any year (2021, 2022, 2023, ..., 2028)

I want to make a visual (like below) that plots per year the annual revenue.

Can you plaese help me? I would really appreciate it!!!

[sevenhills · Accepted solution]

I guess you are doing pro rate by year type of calculations 

In Power Query,

Query Name "Data Table":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjQyNDYE0mb6hvpGBkbGSrE6CCmwjCVExgQsExAYCBQyMTYyBSmAyJiCZVzcgkAyZqYmIBkDoARIzgwsl5iYiDDP0EjfGGZiLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Opportunity name" = _t, #"12_Month_Revenue" = _t, #"Start Operating date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Opportunity name", type text}, {"12_Month_Revenue", Decimal.Type}, {"Start Operating date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "EOY", each Date.EndOfYear([Start Operating date]), type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "End Operating date", each Date.AddYears([Start Operating date], 1
), type date),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Pro Rate Rev Y1", each [12_Month_Revenue] * (Duration.Days([EOY] - [Start Operating date]))/365.00,  
Decimal.Type),
    #"Inserted Subtraction" = Table.AddColumn(#"Added Custom2", "Pro Rate Rev Y2", each [12_Month_Revenue] - [Pro Rate Rev Y1], Decimal.Type)
in
    #"Inserted Subtraction"

Query name "Yearly Data":

let
    Source = #"Data Table",
    #"Removed Columns" = Table.RemoveColumns(Source,{"Opportunity name", "12_Month_Revenue", "EOY", "End Operating date", "Pro Rate Rev Y2"
}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Start Operating date", "date"}, {"Pro Rate Rev Y1", "Pro Rate Rev"}}),
    Source1 = #"Data Table",
    #"Removed Columns1" = Table.RemoveColumns(Source1,{"Opportunity name", "12_Month_Revenue", "Start Operating date", "EOY","Pro Rate Rev Y1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"End Operating date", "date"}, {"Pro Rate Rev Y2", "Pro Rate Rev"}}),

    Source2 = Table.Combine({#"Renamed Columns", #"Renamed Columns1"}),
    #"Extracted Year" = Table.TransformColumns(Source2,{{"date", Date.Year, Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Extracted Year", {"date"}, {{"Pro Rate Rev", each List.Sum([Pro Rate Rev]), type number}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Grouped Rows",{{"date", "Year"}})
in
    #"Renamed Columns2"

Come back to visualizations (Report)

TIA