Help with distributing a number based on remaining months in a year

[artfulmunkeey]

Hi I'm relative new and wondering if anyone could please help me with something that's driving me crazy!

I receive a report which contains a table similar to the above, listing potential projects, their status, start date and annual revenue.

I need to be able to calculate the expected revenue for projects with probability greater than 50% and create a line graph of this data (along with current monthly revenue from another dataset).

To do so I'd like to be able to determine the number of months remaining in the year after the start date, calculate the monthly revenue and display it as a per the second image. 

Alternatively, is there any way of generating a new table, with monthly revenue for each project on individual rows (third image)? This would be much easier to compare to the other dataset produced by MS Project.

Is this possible?

Many thanks in advance!

[mahoneypat · Accepted solution]

Here is one way to get your desired final table in the query editor.  Note - I did OCR to get data from your image and it didn't work perfectly to demonstrate and I am in a different Date locale, so you probably won't need that step.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZGxDoMgEIZfpXFu4h0cIA/QoVu3DsbBVExsjDbW9vmLaG0U6EIg933wH5fnyWXo7+Y2HjA5Jte+sysC2FVyoWk6pZgyYJAUxx/MbOH0LttXOTbOEZPClXAqpuQr3Bbs/tE/y3Ziv0KMJ8eb2gyDqdZUjGtQPOaItQd3PdMalx6ED0tbOFemG5u6cS+4LAxg6SGgqL0yh0Lxx8m2mQhFpmZY+rDefhJNoOLocOXjCME5ZARRA4MGyoykc3TACU9boaSowoOKICV33RQf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Opportunity Name" = _t, Stage = _t, Probability = _t, #"Weighted Revenue in Year Start" = _t, Date = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-150"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Weighted Revenue in Year Start", Currency.Type}, {"Probability", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Months Remaining", each (Number.Round(Duration.TotalDays(Date.EndOfYear([Date]) - [Date])/30)-1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Monthly Revenue", each [Weighted Revenue in Year Start]/[Months Remaining]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Months Remaining", Int64.Type}, {"Monthly Revenue", Currency.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "DatesList", each let listdate = [Date] in List.Transform({0..[Months Remaining]}, each Date.AddMonths(listdate, _))),
    #"Expanded DatesList" = Table.ExpandListColumn(#"Added Custom2", "DatesList"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded DatesList",{{"DatesList", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"DatesList", "Date"}})
in
    #"Renamed Columns"

Final Result

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[artfulmunkeey]

That's great, many thanks for your help! Solved.