March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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
Project. | Requested Resource Start Date | Requested Resource End Date | Requested Hours |
2206550 | 7/1/2020 | 6/30/2021 | 574 |
2245570 | 7/1/2020 | 6/30/2021 | 160 |
2059200 | 7/13/2020 | 9/12/2020 | 120 |
2185104 | 7/20/2020 | 4/2/2021 | 360 |
2221184 | 7/20/2020 | 11/7/2020 | 92 |
2226726 | 7/27/2020 | 9/26/2020 | 100 |
2249584 | 7/28/2020 | 11/6/2020 | 88 |
2249592 | 7/28/2020 | 11/6/2020 | 84 |
2249598 | 7/28/2020 | 11/6/2020 | 40 |
2274084 | 8/2/2020 | 6/30/2021 | 800 |
2224700 | 8/3/2020 | 3/19/2022 | 264 |
1938284 | 8/31/2020 | 11/29/2020 | 424 |
2129676 | 8/31/2020 | 10/16/2020 | 240 |
2264512 | 8/31/2020 | 10/16/2020 | 160 |
2264522 | 8/31/2020 | 10/16/2020 | 120 |
1982174 | 9/7/2020 | 12/31/2020 | 240 |
2140432 | 9/14/2020 | 5/31/2021 | 24 |
2197694 | 9/14/2020 | 12/31/2020 | 400 |
2273416 | 9/14/2020 | 12/11/2020 | 320 |
2310614 | 9/14/2020 | 10/16/2020 | 80 |
2235826 | 9/21/2020 | 11/27/2020 | 320 |
2317108 | 9/21/2020 | 5/5/2021 | 142 |
2317122 | 9/21/2020 | 5/5/2021 | 400 |
1264874 | 9/28/2020 | 10/2/2020 | 40 |
2317120 | 9/28/2020 | 5/5/2021 | 80 |
2314392 | 9/30/2020 | 12/30/2020 | 240 |
2152510 | 10/1/2020 | 12/11/2020 | 558 |
2152550 | 10/1/2020 | 12/11/2020 | 306 |
1946164 | 10/5/2020 | 6/30/2021 | 200 |
1982174 | 10/5/2020 | 10/16/2020 | 240 |
2129676 | 10/5/2020 | 7/5/2022 | 176 |
2215598 | 10/5/2020 | 11/14/2020 | 60 |
2230812 | 10/5/2020 | 1/8/2021 | 720 |
2365568 | 10/12/2020 | 4/25/2021 | 252 |
2353882 | 10/13/2020 | 1/29/2021 | 192 |
1929570 | 10/19/2020 | 11/27/2020 | 240 |
2215580 | 10/19/2020 | 2/5/2021 | 292 |
2355482 | 10/19/2020 | 12/19/2020 | 248 |
2365586 | 10/19/2020 | 4/25/2021 | 140 |
2365594 | 10/19/2020 | 4/25/2021 | 248 |
2365818 | 10/20/2020 | 5/9/2021 | 96 |
2365922 | 10/20/2020 | 4/25/2021 | 708 |
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.
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!
@DSiffredi , refer my blog and attached file , if that can help
User | Count |
---|---|
122 | |
98 | |
89 | |
74 | |
67 |
User | Count |
---|---|
139 | |
115 | |
114 | |
98 | |
98 |