DateDIFF Workign Days Calculation

[busy86]

Hi there,

I used this thread here: https://community.powerbi.com/t5/Desktop/Calculate-Date-and-Time-difference-considering-the-weekends...

To build a report that calculates working days between ticket create and resolved dates to product mean time to resolve, however I now need to include open tickets in the solution. The problem is, open tickets do not yet have a resolved date, so the last day start time is in 1899 and it skews the stats, as the MTTR is negative.

I've tried doing an IF to switch the last day start to the current day where the ticket is closed:

Cal_LastDayStartTime = if('fpscdb002_ws_003 incident'[Cal_Closed]="Yes", DATE (YEAR ('fpscdb002_ws_003 incident'[resolved_date]), MONTH('fpscdb002_ws_003 incident'[resolved_date]), DAY('fpscdb002_ws_003 incident'[resolved_date])) & " 08:30", DATE (YEAR(TODAY()), MONTH (TODAY()), DAY(TODAY()) & " 08:30"))

but I get  "expressions that yield variant data type cannot be used to define calculated columns" - all seemingly related to the IF statement.

Please help!

[Anonymous]

Hi @Anonymous,

Current power bi not allow you direct calculate between different data types(date/text), you can add format function to convert your date values to fix this issue.

Cal_LastDayStartTime =
IF (
    'fpscdb002_ws_003 incident'[Cal_Closed] = "Yes",
    FORMAT (
        DATE ( YEAR ( 'fpscdb002_ws_003 incident'[resolved_date] ), MONTH ( 'fpscdb002_ws_003 incident'[resolved_date] ), DAY ( 'fpscdb002_ws_003 incident'[resolved_date] ) ),
        "General Date"
    ) & " 08:30",
    FORMAT (
        DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) ),
        "General Date"
    ) & " 08:30"
)

Best Regards,

Jack