Forum Discussion

ADSL's avatar
ADSL
Icon for Post Prodigy rankPost Prodigy
3 years ago

Calculate time spent by session

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 f...
Need Help
jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
3 years ago

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" )
    )
)