Forum Discussion

busy86's avatar
busy86
Frequent Visitor
6 years ago

DateDIFF Workign Days Calculation

Hi there,

 

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

 

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!

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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