Forum Discussion
ADSL
Post Prodigy
3 years agoCalculate 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...
jdbuchanan71
Super User
3 years agoThe 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: