Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
joris
Frequent Visitor

calculate estimated service date

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

TeamDateCapacity in hours
a2022-12-8    20
b2022-12-8    30
a2022-12-9    15
b2022-12-9    25

 

Tickets (filtered status = Open)

rankAssigned to TeamEstimated effort in hours
1a10
2a10
3b30
4a10
5b10

 

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

 

3 REPLIES 3
johnt75
Super User
Super User

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
Frequent Visitor

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

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.