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

[Anonymous]

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.00	1/1/2019	12/16/2021
Project 2	$100,000,000.00	2/1/2021	9/19/2023
Project 3	$40,000,000.00	7/12/1905	12/29/1907
Project 4	$35,000,000.00	3/1/2021	2/19/2023
Project 5	$25,000,000.00	4/1/2024	9/23/2025
Project 6	$250,000,000.00	5/1/2023	12/21/2024
Project 7	$500,000,000.00	5/30/2024	5/20/2026
Project 8	$700,000,000.00	7/1/2022	2/15/2025
Project 9	$300,000,000.00	12/1/2022	3/20/2025
Project 10	$125,678,900.00	11/15/2023	9/5/2025
Project 11	$325,000,000.00	7/1/2025	6/21/2027
Project 12	$100,000,000.00	8/1/2023	1/17/2026
Project 13	$30,000,000.00	4/1/2022	8/18/2024
Project 14	$50,000,000.00	6/1/2022	4/21/2024
Project 15	$75,000,000.00	4/1/2019	4/20/2021
Project 16	$8,000,000.00	6/1/2023	9/13/2026
Project 17	$235,000,000.00	9/1/2024	12/25/2025
Project 18	$85,000,000.00	10/1/2022	6/28/2023
Project 19	$175,000,000.00	3/1/2025	8/28/2025
Project 20	$65,000,000.00	1/1/2022	5/31/2022
Project 21	$125,000,000.93	4/1/2019	3/16/2022
Project 22	$100,000,000.93	5/2/2021	12/18/2023
Project 23	$40,000,000.93	10/10/1905	3/28/1908
Project 24	$35,000,000.93	5/30/2021	5/20/2023
Project 25	$25,000,000.93	6/30/2024	12/22/2025
Project 26	$250,000,000.93	7/30/2023	3/21/2025
Project 27	$500,000,000.93	8/28/2024	8/18/2026
Project 28	$700,000,000.93	9/29/2022	5/16/2025
Project 29	$300,000,000.93	3/1/2023	6/18/2025
Project 30	$125,678,900.93	2/13/2024	12/4/2025
Project 31	$325,000,000.93	9/29/2025	9/19/2027
Project 32	$100,000,000.93	10/30/2023	4/17/2026
Project 33	$30,000,000.93	6/30/2022	11/16/2024
Project 34	$50,000,000.93	8/30/2022	7/20/2024
Project 35	$75,000,000.93	6/30/2019	7/19/2021
Project 36	$8,000,000.93	8/30/2023	12/12/2026
Project 37	$235,000,000.93	11/30/2024	3/25/2026
Project 38	$85,000,000.93	12/30/2022	9/26/2023
Project 39	$175,000,000.93	5/30/2025	11/26/2025
Project 40	$65,000,000.93	4/1/2022	8/29/2022
Project 41	$125,000,001.86	6/30/2019	6/14/2022
Project 42	$100,000,001.86	7/31/2021	3/17/2024
Project 43	$40,000,001.86	1/8/1906	6/26/1908
Project 44	$35,000,001.86	8/28/2021	8/18/2023
Project 45	$25,000,001.86	9/28/2024	3/22/2026
Project 46	$250,000,001.86	10/28/2023	6/19/2025
Project 47	$500,000,001.86	11/26/2024	11/16/2026
Project 48	$700,000,001.86	12/28/2022	8/14/2025
Project 49	$300,000,001.86	5/30/2023	9/16/2025
Project 50	$125,678,901.86	5/13/2024	3/4/2026
Project 51	$325,000,001.86	12/28/2025	12/18/2027

desired output can be used in charts like this

[v-yingjl]

Hi @Anonymous ,

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]

Hi @Anonymous 

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

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"