Duration management

[Anonymous]

Hi all,

I have an issue that is too specific to find existing discussions about it:

So I have calculated a duration measure to know the response time excluding weekends.

I found the below piece of code that seem to work and calculate the number of hours:

Delay =

VAR _Start = 'ALL Responded Case Email'[Created On (Email) (Email)]

Var _End = 'ALL Responded Case Email'[First Manual Response]

Return SUMX(

            CALCULATETABLE(

                    Calendar_Table,

                    DATESBETWEEN(Calendar_Table[Date],_Start,_End),

                    Calendar_Table[Workday] = 1

            ),

            MAX(MIN(Calendar_Table[End],_End) - MAX(Calendar_Table[Start],_Start),0) * 24)

This is returning a number of hours as a decimal number.

This is helpful in some regards (count items responded within 1 hour for instance)

But I would also need to display the data in  d hh:mm:ss format.

So I created another column that is reformating as below:

DelayH =

VAR _Hours = INT('ALL Responded Case Email'[Delay])

VAR _Min = 'ALL Responded Case Email'[Delay]-_Hours

VAR _Minutes = INT(_Min*60)

VAR _Sec = _Min-_Minutes/60

VAR _Seconds = INT(_Sec*60)

Return _Hours & ":" & FORMAT(_Minutes,"00") & ":" & FORMAT(_Seconds,"00")

The problem is that this is a text format so I cannot display information such as fastest response time or average response time in this format.

I suspect that the easiest is to use the calculated duration as decimal number and convert it into time, but I couldnt find anything to do so because PBI doesnt seem to handl hours > 24

Any idea? 

[Anonymous]

Hello,

this is great indeed

This recalculates the delay between start and end date but ignores the working days table.

(This piece of code I had)

Delay =

VAR _Start = 'ALL Responded Case Email'[Created On (Email) (Email)]

Var _End = 'ALL Responded Case Email'[First Manual Response]

Return SUMX(

            CALCULATETABLE(

                    Calendar_Table,

                    DATESBETWEEN(Calendar_Table[Date],_Start,_End),

                    Calendar_Table[Workday] = 1

            ),

            MAX(MIN(Calendar_Table[End],_End) - MAX(Calendar_Table[Start],_Start),0) * 24

)

I'm a bit confused between your bit that is creating a new table and my bit that is creating a new column within a table.
I will try and combine them myself, but if this is quick for you, may I ask for some help?

[Anonymous]

Hi @Anonymous - the DAX code was only an example.  Essentially, I have used the code to add a column to a table.  However you can skip this to use the DateDiff function in a measure.  However, since the DateDiff result could be used in many aggregate measures (i.e. SUM, MIN, MAX, AVERAGE), I would recommend adding an column to perform the calculation when the Data is refreshed, rather that including in SUMX, MINX, MAXX expressions.  As for your CALCULATETABLE, I would need more information about the data model with same data to understand what you are trying to achieve.

[Anonymous]

Hi @Anonymous , thank you for your question.  You could consider that the DAX function DATEDIFF - DAX Guide includes SECONDS, MINUTES and HOURS.

Table = 
ADDCOLUMNS(
    SELECTCOLUMNS( 
        { ( DATE( 2023, 3, 31) + TIME( 13, 3, 12) , NOW() ) },
        "Start", [Value1],
        "End", [Value2]
    ),
    "Seconds Difference", DATEDIFF([Start], [End] , SECOND),
    "Minutes Difference", DATEDIFF([Start], [End] , MINUTE),
    "Mod Difference", 
        VAR _diff = DATEDIFF([Start], [End] , SECOND)
        VAR _day = QUOTIENT( _diff , 60 * 60 * 24 )
        VAR _Mday = MOD( _diff , 60 * 60 * 24 )
        VAR _hour = QUOTIENT( _Mday , 60 * 60 )
        VAR _Mhour = MOD( _Mday , 60 * 60 )
        VAR _minute = QUOTIENT( _Mhour , 60  )
        RETURN 
            _day & " days " & _hour & " hours " & _minute & " minutes"
)

You can use these to measure MIN duration then convert result to Days, Hours, Minutes using QUOTIENT and MOD functions.