Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Duration management

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? 
Need Help
Show and Tell

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.