Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
artfulmunkeey
Helper I
Helper I

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

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

 

artfulmunkeey_2-1595266211765.png

 

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. 

 

artfulmunkeey_1-1595265918140.png

 

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.

 

artfulmunkeey_3-1595266551717.png

 

Is this possible?

 

Many thanks in advance!

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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

finalprojects.png

 

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

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Employee
Employee

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

finalprojects.png

 

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

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors