Duration of activity in " hh:ss" and dec formats

DAX DAX Calculations
1

Hi all,

Forum - calc.xlsx (31.9 KB)
Activity table - forum.pbix (28.6 KB)

Could you please help me with my current problem. I need to achieve duration of activity in “hh:ss” and decimals based on given start time ( earliest time) and end time (latest time) . I need to produce two columns for time in decimals and in “hh:ss” format.

Example:
image

image

Would you please help me to figure out my problem.

As always grateful for help.

Iwona

1 Like
2

Hi @Iwona,
Thank you very much for posting your query in the forum.
To calculate the gross time of each Depot I have created the following measures:

  1. Datetime On: Calculate the start date and time for each activity.
Datetime On =

VAR _TimeOn =

TIMEVALUE(

    SUBSTITUTE(

        FORMAT(

            MAX( 'Activity table'[Sign on Time ( hh:ss)] ),

            "00.00"

        ),

        ".", ":"

    )

)

VAR _DateOn = MAX( 'Activity table'[Operational Date] )

VAR _Result = _DateOn + _TimeOn

RETURN

_Result
  1. Datetime Off: Calculate the end date and time of each activity
Datetime Off =

VAR _TimeOn =

TIMEVALUE(

    SUBSTITUTE(

        FORMAT(

            MAX( 'Activity table'[Sign on Time ( hh:ss)] ),

            "00.00"

        ),

        ".", ":"

    )

)

VAR _TimeOff = TIMEVALUE(

    SUBSTITUTE(

        FORMAT(

            MAX( 'Activity table'[Signoff Time( hh:ss)] ),

            "00.00"

        ),

        ".", ":"

    )

)

VAR _DateOn = MAX( 'Activity table'[Operational Date] )

VAR _DateOff = SWITCH(

    TRUE(),

    _TimeOn <= _TimeOff,

    _DateOn,

    _DateOn + 1

)

VAR _Result = _DateOff + _TimeOff

RETURN

_Result
  1. Total Gross Time dec by Depot: Calculate the Gross time in minutes for each Depot.
Total Gross Time in Minutes by Depot = 
VAR _Virtualtable =
ADDCOLUMNS(
  'Activity table',
    "@DateTimeOn", [Datetime On],
    "@DateTimeOff", [Datetime Off]
) 
VAR _TableByDepot = ADDCOLUMNS(
    SUMMARIZE( _Virtualtable,'Activity table'[Depot] ),
    "@MinDateTimeOn", MINX( _Virtualtable, [@DateTimeOn] ),
    "@MaxDateTimeOff", MAXX( _Virtualtable, [@DateTimeOff] )
) 
VAR _MinDateTime = MAXX( _TableByDepot, [@MinDateTimeOn] ) 
VAR _MaxDateTime = MAXX( _TableByDepot, [@MaxDateTimeOff] ) 
VAR _Result = DATEDIFF( _MinDateTime, _MaxDateTime, MINUTE ) 

RETURN
_Result
  1. Total Gross Time hh:ss by Depot: Gross time in hh:ss format for each Depot
Total Gross Time hh:ss by Depot = 
VAR _TotalMin = [Total Gross TIME IN Minutes by Depot] 
VAR _Hour = INT( DIVIDE( _TotalMin, 60 ) ) 
VAR _Min = MOD( _TotalMin, 60 ) 
VAR _Result = FORMAT( _Hour, "00" ) & ":" & FORMAT( _Min, "00" ) 

RETURN
_Result
  1. Total Gross Time dec by Depot: Gross time in dec for each depot
Total Gross Time dec by Depot = 
VAR _TotalMin = [Total Gross TIME IN Minutes by Depot] 
VAR _Hour = INT( DIVIDE( _TotalMin, 60 ) ) 
VAR _Min = MOD( _TotalMin, 60 ) 
VAR _Result = _Hour + DIVIDE( _Min, 60 ) 

RETURN
_Result

image
image824×462 16.4 KB

I have also created the calculation for real times:

Time in Minutes =

VAR _TimeOn =

TIMEVALUE(

    SUBSTITUTE(

        FORMAT(

            MAX( 'Activity table'[Sign on Time ( hh:ss)] ),

            "00.00"

        ),

        ".", ":"

    )

)

VAR _TimeOff = TIMEVALUE(

    SUBSTITUTE(

        FORMAT(

            MAX( 'Activity table'[Signoff Time( hh:ss)] ),

            "00.00"

        ),

        ".", ":"

    )

)

VAR _DateOn = MAX( 'Activity table'[Operational Date] )

VAR _DateOff = SWITCH(

    TRUE(),

    _TimeOn <= _TimeOff,

    _DateOn,

    _DateOn + 1

)

VAR _DateTimeOn = _DateOn + _TimeOn

VAR _DateTimeOff = _DateOff + _TimeOff

VAR _Result = DATEDIFF( _DateTimeOn, _DateTimeOff, MINUTE )

RETURN

_Result


Time dec =

VAR _TotalMin =

[Time IN Minutes]

VAR _Hour = INT( DIVIDE( _TotalMin, 60 ) )

VAR _Min = MOD( _TotalMin, 60 )

VAR _Result = _Hour + DIVIDE( _Min, 60 )

RETURN

_Result


Time hhss =

VAR _TotalMin = [Time IN Minutes]

VAR _Hour = INT( DIVIDE( _TotalMin, 60 ) )

VAR _Min = MOD( _TotalMin, 60 )

VAR _Result = TIMEVALUE(

    FORMAT( _Hour, "00" ) & ":" & FORMAT( _Min, "00" )

)

RETURN

_Result


Total Time dec =

VAR _Virtualtable =

ADDCOLUMNS( 'Activity table', "@Timedec", [Time dec] )

VAR _Result = SUMX( _Virtualtable, [@Timedec] )

RETURN

_Result


Total Time hh:ss =

VAR _Virtualtable =

ADDCOLUMNS(

  'Activity table',

    "@TimeinMinutes", [Time IN Minutes]

)

VAR _TotalMin = SUMX( _Virtualtable, [@TimeinMinutes] )

VAR _Hour = INT( DIVIDE( _TotalMin, 60 ) )

VAR _Min = MOD( _TotalMin, 60 )

VAR _Result = FORMAT( _Hour, "00" ) & ":" & FORMAT( _Min, "00" )

RETURN

_Result

image
image821×465 11.4 KB

Regards

Activity table - forum_JAFP.pbix (36.5 KB)

3 Likes
3

Hi @Iwona, did the response provided by @jafernandezpuga help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

4

Hi @Iwona , we noticed that no response was received from you with regard to our post last Oct 24th.

We just want to check if you still need further help with this post.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @Iwona

I’d like to check again with you if the answer @jafernandezpuga provided above helped you solve your inquiry.

If it does, kindly mark the answer as the solution that solved your query.

If not, how far did you get, and what kind of help do you need further?

6

Hi @Iwona did the response above help solve your query? It’s been a while since we got a response from you.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.