Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

2 REPLIES 2
Jihwan_Kim
Super User
Super User

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.

 

Picture1.png

 

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.


Go to My LinkedIn Page


Anonymous
Not applicable

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.  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors