Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |