cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sbowman
Frequent Visitor

Generating a Normal Distribution for Budget by project when given start anGenerating a Nod end dates

How can I take a table of projects, that features a budget value, and a start and end date, and generate a normalized distribution by month for every project? 

 

Ideally, this would generate a new table that would have Project Name, Date, Percentage Value (or Budget Value) applicable to that month. Allowing you to forecast ahead overall, or drill into individual projects or corresponding project metadata (region, geography, type, etc.)

 

What's the best approach? It would be fine to implement this in SQL directly, in M code in data transform, or in DAX expression/measure.

 

https://drive.google.com/file/d/1yl5X6_MWcJr5OiQsG7QRTCarZ1iMC2j2/view?usp=sharing 

 

Project NameTotal BudgetStart DateEnd Date

Project 1$125,000,000.001/1/201912/16/2021
Project 2$100,000,000.002/1/20219/19/2023
Project 3$40,000,000.007/12/190512/29/1907
Project 4$35,000,000.003/1/20212/19/2023
Project 5$25,000,000.004/1/20249/23/2025
Project 6$250,000,000.005/1/202312/21/2024
Project 7$500,000,000.005/30/20245/20/2026
Project 8$700,000,000.007/1/20222/15/2025
Project 9$300,000,000.0012/1/20223/20/2025
Project 10$125,678,900.0011/15/20239/5/2025
Project 11$325,000,000.007/1/20256/21/2027
Project 12$100,000,000.008/1/20231/17/2026
Project 13$30,000,000.004/1/20228/18/2024
Project 14$50,000,000.006/1/20224/21/2024
Project 15$75,000,000.004/1/20194/20/2021
Project 16$8,000,000.006/1/20239/13/2026
Project 17$235,000,000.009/1/202412/25/2025
Project 18$85,000,000.0010/1/20226/28/2023
Project 19$175,000,000.003/1/20258/28/2025
Project 20$65,000,000.001/1/20225/31/2022
Project 21$125,000,000.934/1/20193/16/2022
Project 22$100,000,000.935/2/202112/18/2023
Project 23$40,000,000.9310/10/19053/28/1908
Project 24$35,000,000.935/30/20215/20/2023
Project 25$25,000,000.936/30/202412/22/2025
Project 26$250,000,000.937/30/20233/21/2025
Project 27$500,000,000.938/28/20248/18/2026
Project 28$700,000,000.939/29/20225/16/2025
Project 29$300,000,000.933/1/20236/18/2025
Project 30$125,678,900.932/13/202412/4/2025
Project 31$325,000,000.939/29/20259/19/2027
Project 32$100,000,000.9310/30/20234/17/2026
Project 33$30,000,000.936/30/202211/16/2024
Project 34$50,000,000.938/30/20227/20/2024
Project 35$75,000,000.936/30/20197/19/2021
Project 36$8,000,000.938/30/202312/12/2026
Project 37$235,000,000.9311/30/20243/25/2026
Project 38$85,000,000.9312/30/20229/26/2023
Project 39$175,000,000.935/30/202511/26/2025
Project 40$65,000,000.934/1/20228/29/2022
Project 41$125,000,001.866/30/20196/14/2022
Project 42$100,000,001.867/31/20213/17/2024
Project 43$40,000,001.861/8/19066/26/1908
Project 44$35,000,001.868/28/20218/18/2023
Project 45$25,000,001.869/28/20243/22/2026
Project 46$250,000,001.8610/28/20236/19/2025
Project 47$500,000,001.8611/26/202411/16/2026
Project 48$700,000,001.8612/28/20228/14/2025
Project 49$300,000,001.865/30/20239/16/2025
Project 50$125,678,901.865/13/20243/4/2026
Project 51$325,000,001.8612/28/202512/18/2027

 

desired output can be used in charts like this

sbowman_0-1621526836011.png

 

2 REPLIES 2
v-yingjl
Community Support
Community Support

Hi @sbowman ,

If you want to claculate the normal distribution for each project under current data source, I'm afraid you could not do that because there is only single value for each project. Under this senario the parameter standard deviation will be 0 which is not allowed to be used in the formula Norm.Dist().

 

If my understanding is not correct, you can consider sharing more details about this issue.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Vera_33
Super User
Super User

Hi @sbowman 

 

This is one way we did last year, average the budget to day level, connect the table to Date table then do further analysis

Vera_33_0-1621566109969.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZZNbl0hDIW3Ur1x9C62+d1F5lFGVSedVKq6fxWDARuIMgjiHgzHh498fb0+//75/evnvx/w+ngBBtd+6t8OnvqLDgpPxAewDhBe3x9LgzzlnNHIZ3WiPK7wgIyG6pRfkrqsSw8UF+oAC5epg2Qkvk6R3RnpKnhWaatZieeveCkk2VgwkqgkQxP6ynUCxABvNKlOBWUAOdG0OmMQjSbXqbSZlviz5maQ49i9lba20fTtoNShUwPd3hBTLt3q8ACMA9Wd3UwA9pQ251L/rrngIg9sf+CWgzwqQepB2mwAUmfSLWqLZVnA2g1e+S2aOG1YPTYa3ng6o9Byzdb5M9fAWchnmXackZ7tOKkly9YpMwpB+rW5zVnIRgNunifPqkbT7mO6XIcgonwWQv4wnpe7FaIRdLQaQ4R32b2rTOh1N5kJQ5ehFIDeWrhcWNRceJfmtGM3BhoyV6uDbGWaDV027yDoO7hV03xYsjj7hdd+oWHEu4gsKUrcbiIaTDQZroCrtNtIoSFFlw2mojQgXKoZWMy+0QzwyJWVkcFFk3HUcWyyNv5mCRlg2E0G/QpYZNCZEnIS/gENf1pCGhpb38SSjjjLANLcWLKsCZouMo0OW63fgCIySw9S9NiKUX/xupHb0QxApiOgENJ7uMk0QmY19TjE+ytsKDKTHCZHhpG2215zpFcz3ObW55MJXqMEZrVlpJ89NDIdEpHBtLyHhM62eYUSeGfuRt0Us7W4KJbEEyVeoURk85aCvqXWSa9QomQL/Xjtm9cogcEEdQHK9ZZ6jRKRRZWSeQG2aholq9pMCTfg8mx4jRKwKSEh0OWfiKBRAhMl8x+jmpebJUGjZNtk0C9HRcn3fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Total Budget" = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Total Budget", type number}, {"Start Date", type date}, {"End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DayLevel", each [Total Budget]/(Number.From([End Date]-[Start Date])+1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "DatesList", each List.Transform( {Number.From([Start Date]).. Number.From([End Date])}, each Date.From(_))),
    #"Expanded DatesList" = Table.ExpandListColumn(#"Added Custom1", "DatesList")
in
    #"Expanded DatesList"

 

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors