cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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

3 REPLIES 3
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 =
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``````
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....

Super User

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 =
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``````

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors