Forum Discussion

DSiffredi's avatar
DSiffredi
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

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 hav...
Need Help
v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
5 years ago

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_SlgS55FiTRsVsOY2akBw4K-v_JGK7llFvl3OAOjJQ?e=MkyYrO

 

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.

DSiffredi's avatar
DSiffredi
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

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!