calculate estimated service date

[joris]

I'm working on the following use case:

It involves a service desk and the estimated date that a ticket will be processed. At any certain day, there will be an X amount of capacity per team to process tickets (eg 20 hrs on 2022-12-8, 15 on 2022-12-9 etc). Each ticket contains an estimated effort to complete it. The challenge is to estimate when a ticket will be picked up by the service desk based off the ticket rank and the available capacity.


Tables:

Capacity

Team	Date	Capacity in hours
a	2022-12-8	20
b	2022-12-8	30
a	2022-12-9	15
b	2022-12-9	25

Tickets (filtered status = Open)

rank	Assigned to Team	Estimated effort in hours
1	a	10
2	a	10
3	b	30
4	a	10
5	b	10

Capacity table is connected to date table and team table. Ticket table is only connected to team table (since I need to calculate the estimated date still)

Goal: 

In my example, tickets rank 1 and 2 will be processed on 2022-12-8 by team a, and ticket rank 4 will be processed on 2022-12-9. 

I've come as far to calculate  on a per day basis (=capacity 8 hours per day) using Current Date as a starting point in the following measure:

Estimated date = 

--calculate estimated effort in days

var _remwork = DIVIDE (CALCULATE(SUM(Tickets[Estimated Effort]), FILTER(ALL(Tickets),Tickets[Rank]<SELECTEDVALUE(Tickets[Rank]))),8,0)
var _currentDate = Today()
var _networkingdays = FILTER(ALL(Datum),Datum[Datum]>_currentDate && Datum[Weekday] = "Yes")
var _Datecomplete = TOPN(ROUND(_remwork,0),_networkingdays,Datum[Datum],asc)
var _result = MAXX(_Datecomplete,Datum[Datum])
return
_result

I'm struggling to compare the actual capacity per given date with the cumulative estimated effort of all tickets. That means that in the first step, calculating the effort, I somehow have to incorporate the available capacity and when the cumulative estimated effort exceeds this for any given day, it will add a day to the estimated date and so on. What I can't seem to wrap my head around is that the capacity has assigned dates, but the tickets have not, making it hard to compare it on that dimension.  Any idea is welcome (DAX, Power Query, both).

[johnt75]

If you have a many-to-many bidirectional filter between team and ticket, you can try

Date to start = VAR CurrentRank = SELECTEDVALUE( 'Tickets'[rank])
VAR CurrentTeam = SELECTEDVALUE( 'Tickets'[Assigned to Team])
VAR EffortBeforeCurrentTicket = 
	CALCULATE( 
		SUM( 'Tickets'[Estimated effort in hours]), 
		REMOVEFILTERS( 'Tickets'),
		'Tickets'[rank] <= CurrentRank && 'Tickets'[Assigned to Team] = CurrentTeam
	)
VAR CapacityTable =
	ADDCOLUMNS(
		CALCULATETABLE(
			SUMMARIZE( 'Team Capacity', 'Date'[Date] ),
			'Date'[Date] >= TODAY()
		),
		"Total Capacity End Of Day",
		VAR  CurrentVisibleDate = 'Date'[Date]
		RETURN
		CALCULATE( SUM( 'Team Capacity'[Capacity in hours] ), 
			'Date'[Date] <= CurrentVisibleDate && 'Date'[Date] >= TODAY()
		)
	)
VAR DateOfStart = MINX(
	FILTER( CapacityTable, [Total Capacity End Of Day] >= EffortBeforeCurrentTicket),
	'Date'[Date]
)
RETURN DateOfStart

[joris]

thanks, I found a Power Query solution, but I'll definitely try this one as well. 
The relationship between Team and Ticket is one-to-many, does that matter? I can set the filter to bidirectional off course....

[johnt75]

I missed that there was a team table as well as capacity, which is why I thought it would have to be many to many. one-to-many is much better. try this amended code

Date to start = VAR CurrentRank = SELECTEDVALUE( 'Tickets'[rank])
VAR CurrentTeam = SELECTEDVALUE( 'Tickets'[Assigned to Team])
VAR EffortBeforeCurrentTicket = 
	CALCULATE( 
		SUM( 'Tickets'[Estimated effort in hours]), 
		REMOVEFILTERS( 'Tickets'),
		'Tickets'[rank] <= CurrentRank && 'Tickets'[Assigned to Team] = CurrentTeam
	)
VAR CapacityTable =
	ADDCOLUMNS(
		CALCULATETABLE(
			SUMMARIZE( 'Team Capacity', 'Date'[Date] ),
			'Date'[Date] >= TODAY()
		),
		"Total Capacity End Of Day",
		VAR  CurrentVisibleDate = 'Date'[Date]
		RETURN
		CALCULATE( SUM( 'Team Capacity'[Capacity in hours] ), 
			'Date'[Date] <= CurrentVisibleDate && 'Date'[Date] >= TODAY(),
            TREATAS( { CurrentTeam }, 'Team'[Team] )
		)
	)
VAR DateOfStart = MINX(
	FILTER( CapacityTable, [Total Capacity End Of Day] >= EffortBeforeCurrentTicket),
	'Date'[Date]
)
RETURN DateOfStart