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 and 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

 

1 REPLY 1
v-lionel-msft
Community Support
Community Support

Hi @sbowman ,

 

SUMMARIZE function (DAX) - DAX | Microsoft Docs

Try to create a calculated table with this function/

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!