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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DSiffredi
Microsoft Employee
Microsoft Employee

Spreading values over time (total requested hours over time)

Hi there, I hope everybody is doing great. 
I've been trying to crack this nut for some hours but I'm not finding the right way to do it - so I rather ask for help. 

the situation is simple - I have projects that have start and end dates , total requested hours and I want to be able to spread the requested hours over the months since the project start till it ends. For example a project that has 100hrs requested over two months , I want to be able to put that into a visual but instead of seeing 100hrs on the starting date of the project , I want to be able to see 50 hrs on month 1 and 50 hrs on month 2. if the project runs for 10 months and its a 1000 hrs project , then I want to be able to see 100 hrs x month over the 10 months of the project. unfortunately there's no granularity on the data I'm getting and I don't know how create that table . 

 

I'm attaching a picture of the issue (not the solution but I hope the explanation makes it easy to follow). and adding a simple table with the minimum data I guess we need to create a solution (the model is way to big and sensitive to share more than this). 
thanks in advance 

 

DSiffredi_0-1614216714882.png

Project.Requested Resource Start DateRequested Resource End DateRequested Hours
22065507/1/20206/30/2021574
22455707/1/20206/30/2021160
20592007/13/20209/12/2020120
21851047/20/20204/2/2021360
22211847/20/202011/7/202092
22267267/27/20209/26/2020100
22495847/28/202011/6/202088
22495927/28/202011/6/202084
22495987/28/202011/6/202040
22740848/2/20206/30/2021800
22247008/3/20203/19/2022264
19382848/31/202011/29/2020424
21296768/31/202010/16/2020240
22645128/31/202010/16/2020160
22645228/31/202010/16/2020120
19821749/7/202012/31/2020240
21404329/14/20205/31/202124
21976949/14/202012/31/2020400
22734169/14/202012/11/2020320
23106149/14/202010/16/202080
22358269/21/202011/27/2020320
23171089/21/20205/5/2021142
23171229/21/20205/5/2021400
12648749/28/202010/2/202040
23171209/28/20205/5/202180
23143929/30/202012/30/2020240
215251010/1/202012/11/2020558
215255010/1/202012/11/2020306
194616410/5/20206/30/2021200
198217410/5/202010/16/2020240
212967610/5/20207/5/2022176
221559810/5/202011/14/202060
223081210/5/20201/8/2021720
236556810/12/20204/25/2021252
235388210/13/20201/29/2021192
192957010/19/202011/27/2020240
221558010/19/20202/5/2021292
235548210/19/202012/19/2020248
236558610/19/20204/25/2021140
236559410/19/20204/25/2021248
236581810/20/20205/9/202196
236592210/20/20204/25/2021708
3 REPLIES 3
v-kkf-msft
Community Support
Community Support

Hi,

Try the following formula:

Value = 
var MonthDiff = 
    DATEDIFF(
        MAX(PROJECT[Requested Resource End Date]),
        MAX(PROJECT[Requested Resource Start Date]),
        MONTH
    )
var _average = 
    DIVIDE(
        MAX(PROJECT[Requested Hours]),
        -MonthDiff
    )
var EndDate = 
    EDATE(
        MAX(PROJECT[Requested Resource Start Date]),
        -MonthDiff-1
    )
return 
    IF(
        MAX('Calendar'[Date]) >= MAX(PROJECT[Requested Resource Start Date]) 
        && MAX('Calendar'[Date]) <= EndDate,
        _average,
        BLANK()
    )

This is my PBIX file.

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-lazhang_microsoft_com/ES2S_SlgS55FiTRsVsOY2a...

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

thanks for taking the time, unfortunately this doesn't do what I was looking for and is sensitive to date granularity which shouldnt be. 

thanks again! 

amitchandak
Super User
Super User

@DSiffredi , refer my blog and attached file , if that can help

 

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

October NL Carousel

Fabric Community Update - October 2024

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