powerbi - Power BI 计算不包括周末和非工作时间的时差

Question

我有下表并计算了 之间的持续时间First_change_Date 和 创建日期 使用这个 DAX 公式:

Response_time = 
    VAR Minutes = DATEDIFF('otrs ticket Response'[otrs ticket.create_time], 'otrs ticket Response'[First_Change_time],MINUTE)
    var days =INT(Minutes/1440)
    var hourNo=INT(MOD(Minutes,1440) / 60)
    var minuteNO=MOD(Minutes,60)
RETURN
    CONCATENATE( CONCATENATE( CONCATENATE(days,"d "), CONCATENATE(hourNo, "H ")), CONCATENATE(minuteNO, "m "))

我想排除周末(周五、周六)和非工作时间(下午 5:00 - 上午 9:00)
数据:

Answer 1

对于我的客户，我创建了一个逻辑。首先创建了一个WorkingHoursTable .

然后使用以下公式在具有开始和结束日期时间的表中创建一个计算列。

Working Hours Between Dates = 
var startDate = [yourStartDateTime].[Date]
var startTime = [yourStartDateTime] - startDate
var endDate = [yourEndDateTime].[Date]
var endTime = [yourEndDateTime] - endDate

var firstFullDay = startDate + 1
var lastFullDay = endDate - 1

var inBetweenWorkingHours = 
    IF(
        firstFullDay > lastFullDay,
        0,
        SUMX(CALENDAR(firstFullDay, lastFullDay), LOOKUPVALUE(WorkingHoursTable[WorkingHoursInAllDay], WorkingHoursTable[WeekDay], WEEKDAY([Date], 2)))
    )

var firstDayStart = LOOKUPVALUE(WorkingHoursTable[StartTime], WorkingHoursTable[WeekDay], WEEKDAY(startDate, 2))
var firstDayEnd = LOOKUPVALUE(WorkingHoursTable[EndTime], WorkingHoursTable[WeekDay], WEEKDAY(startDate, 2))

var lastDayStart = LOOKUPVALUE(WorkingHoursTable[StartTime], WorkingHoursTable[WeekDay], WEEKDAY(endDate, 2))
var lastDayEnd = LOOKUPVALUE(WorkingHoursTable[EndTime], WorkingHoursTable[WeekDay], WEEKDAY(endDate, 2))

var effectiveStartTime = IF(startTime < firstDayStart, firstDayStart, startTime)
var effectiveEndTime = IF(endTime > lastDayEnd, lastDayEnd, endTime)
        
return 
    IF(
        startDate = endDate,
        24 * IF(effectiveEndTime > effectiveStartTime, effectiveEndTime - effectiveStartTime, 0),
        var firstDayWorkingHour = 
            24 *
            IF(
                startTime > firstDayEnd,
                0,
                firstDayEnd - effectiveStartTime
            )

        var lastDayWorkingHour = 
            24 *
            IF(
                endTime < lastDayStart,
                0,
                effectiveEndTime - lastDayStart
            )
        
        return firstDayWorkingHour + lastDayWorkingHour + inBetweenWorkingHours
    )

在此公式中，您只需正确设置前 4 个变量。然后它将计算总工作时间。单位将以小时为单位。
编辑:正如我从您的帖子中看到的，您的周末是周五和周六，您需要使用略有不同的 WEEKDAY 函数。您可以将 1 作为第二个参数发送给 WEEKDAY 函数，而不是 2。您还需要修改 WeekDay WorkingHoursTable的栏目.
在此之后，您可以使用公式将其解析为“9d 20H 52m”。