Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all.
I have a bit of a challenge, I hope all you smart people can help me with. 🙂
I have made a Report for one of our product lines, but I am strugling with the final bit.
I want to see when the line has free hours, so we know when we can book other orders.
Right now, the report book all hours by shipment date, not concidering the max hours we can use.
I need a Dax formula that moves overbooked hours to earlier date if, the shipment day is full.
So if we have booked 5 more hours than the line can manage on the 15/10, then those 5 hours should be allocated to 14/10. If that leaves 2 more hours than we can handle on the 14/10, then those hours should be allocated to 13/10 (Today)
Once we reach today, all remaining hours should be placed there no matter what.
That way we can easily spot if we have overbooked the line.
Here is an example of what it looks like, and what i need. For the example the maximum hours avaliable each day is 11.
Here is the fake raw data, you can put in Power BI. 🙂
Delivery Date | Project | Hours |
14-10-2020 | A | 2 |
15-10-2020 | B | 7 |
16-10-2020 | C | 5 |
14-10-2020 | D | 4 |
17-10-2020 | E | 6 |
17-10-2020 | F | 9 |
14-10-2020 | G | 4 |
17-10-2020 | H | 8 |
Hope someone has a bright idea for this issue. 🙂
Solved! Go to Solution.
Hi, @Thim
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
T1:
T2:
T3:
You may create a new blank query with the following m codes.
(tab as table)=>
let
Source = tab,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Delivery Date", type date}, {"Project", type text}, {"Hours", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Delivery Date"}, {{"TotalHours", each List.Sum([Hours]), type nullable number}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
date = [Delivery Date],
list = Table.SelectRows(
#"Grouped Rows",
each [Delivery Date]>=date
)[TotalHours],
s = List.Reverse(list),
val = List.Accumulate(
s,
0,
(s,c)=>if s+c>11 then s+c-11 else 0
)
in
val
),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let
d = [Delivery Date],
v = Table.SelectRows(#"Added Custom",each [Delivery Date]>d),
x = Table.Min(v,"Delivery Date")[Custom]
in
if [Delivery Date]=List.Min(#"Added Custom"[Delivery Date])
then if [Custom]>0 then 11+[Custom] else [TotalHours]
else if [Custom]>0 then 11 else x+[TotalHours])
in
#"Added Custom1"
Then you may invoke the function by entering parameter 'T1', 'T2', 'T3' and get three tables.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Thim
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
T1:
T2:
T3:
You may create a new blank query with the following m codes.
(tab as table)=>
let
Source = tab,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Delivery Date", type date}, {"Project", type text}, {"Hours", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Delivery Date"}, {{"TotalHours", each List.Sum([Hours]), type nullable number}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
date = [Delivery Date],
list = Table.SelectRows(
#"Grouped Rows",
each [Delivery Date]>=date
)[TotalHours],
s = List.Reverse(list),
val = List.Accumulate(
s,
0,
(s,c)=>if s+c>11 then s+c-11 else 0
)
in
val
),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let
d = [Delivery Date],
v = Table.SelectRows(#"Added Custom",each [Delivery Date]>d),
x = Table.Min(v,"Delivery Date")[Custom]
in
if [Delivery Date]=List.Min(#"Added Custom"[Delivery Date])
then if [Custom]>0 then 11+[Custom] else [TotalHours]
else if [Custom]>0 then 11 else x+[TotalHours])
in
#"Added Custom1"
Then you may invoke the function by entering parameter 'T1', 'T2', 'T3' and get three tables.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers. Just what i needed. 🙂
@Thim
Can you add this as a measure and try?
Total Hours Allocated =
VAR _LastestDate = CALCULATE(MIN(Booking[Delivery Date]),ALLSELECTED(Booking[Delivery Date]))
VAR _CountAll = CALCULATE(COUNTROWS(VALUES(Booking[Delivery Date])),Booking[Delivery Date])
VAR _TotalHours = CALCULATE(SUM(Booking[Hours]),ALLSELECTED(Booking[Delivery Date]))
return
IF(
MAX(Booking[Delivery Date]) <> _LastestDate,
11,
_TotalHours - (11 * _CountAll- 1 ) + SUM(Booking[Hours])
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks for the reply, and helping. 🙂
The meassure works, when we have overbooked the line, but once there are free hours it gets a bit funky.
Here is an example of the result, when i tweak the booked hours.
At this situation it should simply accept all dates with their booked hours, as all days are belov 11.
@Thim
show me all the scenarios and the expected results.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
First example, is when we have overbooked.
Second example, is when 1 day is overbooked.
third example, is when several days are overbooked, but there are days in between.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |