Calculate time spent by session

[ADSL]

Hi BI Community Team,

With screenshot below, we're looking for the time spent:

1. By session - early morning, morning, lunch, afternoon, evening & late evening.

2. Total time spent

sample file - https://drive.google.com/drive/folders/1EsiFHTEmDmDu4VJ1DCvXqjLPEf6ya_bk?usp=drive_link 

Any suggestion/advise?

Thanks and Regards,

[jdbuchanan71]

The number above is the time spent between the earliest log_in and the latest log_out which may not be what you are looking for.  It looks like each row is a separate instance so we can calcualte the time in seconds for each row with a calculated column.

Time Spent Seconds = DATEDIFF(VISIT_SUMM[TIME_IN],VISIT_SUMM[TIME_OUT],SECOND)

Then a measure to sum the time in seconds.

Duration Seconds = SUM ( [Time Spent Seconds] )

Then the duration measure becomes this.

Time Spent = 
VAR _Seconds = [Duration Seconds]
VAR _Minutes = INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds = MOD ( _Seconds, 60 )
VAR _Hours = INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes = MOD ( _Minutes, 60 )

RETURN
IF  ( NOT ISBLANK ( _Seconds ), 
    SWITCH ( TRUE(),
    _Seconds < 60, FORMAT ( _RemainingSeconds, "0" ),
    _Seconds < 3600,  FORMAT ( _RemainingMinutes, "0" ) & ":" & 
                    FORMAT ( _RemainingSeconds, "00" ),
    FORMAT ( _Hours, "#,#" ) & ":" & 
    FORMAT ( _RemainingMinutes, "00" ) & ":" & 
    FORMAT ( _RemainingSeconds, "00" )
    )
)

One good thing about doing it this way is you can summarize it at whatever level you want and the amounts still sum:

[jdbuchanan71]

In your visual, your End Time field should be looking at the Latest 'VISIT_SUMM'[Log_Out] I think.

Then a measure like this will give us the time format duration.

Time Spent = 
VAR _Start = MIN ( VISIT_SUMM[TIME_IN] )
VAR _End = MAX ( VISIT_SUMM[TIME_OUT] )
VAR _Seconds = DATEDIFF ( _Start, _End, SECOND )
VAR _Minutes = INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds = MOD ( _Seconds, 60 )
VAR _Hours = INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes = MOD ( _Minutes, 60 )

RETURN
IF  ( NOT ISBLANK ( _Seconds ), 
    SWITCH ( TRUE(),
    _Seconds < 60, FORMAT ( _RemainingSeconds, "0" ),
    _Seconds < 3600,  FORMAT ( _RemainingMinutes, "0" ) & ":" & 
                    FORMAT ( _RemainingSeconds, "00" ),
    FORMAT ( _Hours, "#,#" ) & ":" & 
    FORMAT ( _RemainingMinutes, "00" ) & ":" & 
    FORMAT ( _RemainingSeconds, "00" )
    )
)