Effort calculation for working days

DAX DAX Calculations
1

Hi All,

I’m working to write a measure to get the calculation of the theoretical effort spent on a ticket.
Each ticket has a creation and, when resolved, a resolution date.

I need to compute the theoretical effort spent on each ticket considering 8 hours per working days and 0 for Saturday and Sunday, considering the dates between the Create and End date.

This below is my attempt and even if very “naive” it’s working, but soon I’ll need to consider not only the day but also the hours when tickets are created and closed.

Is there any other smarter way to face this kind of pattern?

I’ve been looking for other similar topics, but I found none.
I don’t need an off the shelf solution, but some hint would be really appreciated.

Thanks a lot

Roberto

Theoretical Effort (hrs) = 
VAR _Start =
    MIN ( Tickets[Created date] )
VAR _End =
    if(ISBLANK(MAX(Tickets[Resolved date])), TODAY(), MAX ( Tickets[Resolved date] ))
VAR _NumOfDays =
    DATEDIFF ( _Start, _End, DAY )
VAR _Dates =
    DATESINPERIOD ( 'Calendar'[Date], _Start, _NumOfDays, DAY )
VAR _WorkingDayHours = 8
RETURN
    (
        COUNTROWS ( FILTER ( _Dates, WEEKDAY ( 'Calendar'[Date], 2 ) <= 5 ) ) + 1
    ) * _WorkingDayHours

Theorical Effort.xlsx (18.7 KB) Theorical Effort.pbix (77.5 KB)

2

Try something like the below

I’m going to have to work with this column here

image

Days (Minus Weekends) = 
VAR StartDate = MIN( Tickets[Create] )
VAR EndDate = MAX( Tickets[End] )

RETURN
CALCULATE( COUNTROWS( 'Calendar' ),
    FILTER( ALL( 'Calendar' ), 'Calendar'[Date] >= StartDate && 'Calendar'[Date] <= EndDate ),
        FILTER( ALL( 'Calendar' ), 'Calendar'[DayInWeek] <> 6 && 'Calendar'[DayInWeek] <> 0 ) )

Sam

1 Like