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
Thim
Resolver V
Resolver V

Allocate limited ressources per day

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.

 

image.png

 

 

Here is the fake raw data, you can put in Power BI. 🙂

 

Delivery DateProjectHours
14-10-2020A2
15-10-2020B7
16-10-2020C5
14-10-2020D4
17-10-2020E6
17-10-2020F9
14-10-2020G4
17-10-2020H8

 

Hope someone has a bright idea for this issue. 🙂

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Thim 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

T1:

f1.png

 

T2:

f2.png

 

T3:

f3.png

 

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.

f4.png

f5.png

f6.png

 

Best Regards

Allan

 

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

View solution in original post

6 REPLIES 6
v-alq-msft
Community Support
Community Support

Hi, @Thim 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

T1:

f1.png

 

T2:

f2.png

 

T3:

f3.png

 

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.

f4.png

f5.png

f6.png

 

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. 🙂

Fowmy
Super User
Super User

@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])
)

Fowmy_0-1602592463458.png

 

________________________

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 🙂

YouTube  LinkedIn

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


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.

 

image.png

 

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 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


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.

 

image.png

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.