cancel
Showing results 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

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors