Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
I am not sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below.
https://www.dropbox.com/s/yyprs9v5rpjqwg4/sbowman.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
This is really close to what I am trying to achieve. Whereas your solution evenly split the budget amongst the applicable date values, I am trying to generate a normal (or Bell curve) distribution of the project budget value across that timeframe for each project. So in the first month there would be very little budget, same as the last month, in the next more, and more, and at the midway date it should be the mean of the values.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |