Forum Discussion
nicoenz
Helper III
Helper IIISplit revenue per year based to create visual
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!!!
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
1 Reply
sevenhillsSuper User
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