Reshape data

Hi Anonymous,

I plugged your demo time data in along with dates to look something like this:

After loading the data I added two calculated columns to my datetime table: HourEnd and OnlineUsers.  HourEnd is just HourStart + 59 minutes to give us a range of time.  You could also probably do +60 minutes so long as you took off the "or = to" modifier on the OnlineUsers calculated column.

HourEnd: Take the start time and add 59 minutes

HourEnd = Table2[HourStart] + 59/(60*24)

OnlineUsers: count the rows in Table1 that fall within the hour

OnlineUsers = CALCULATE(
	COUNTROWS(Table1),
	FILTER(Table1,Table2[HourStart] >= Table1[Start Time]
		&&
		Table2[HourEnd] <= Table1[End Time])
)

End result is something like this:

If you build a relationship between your tables on Start Date then you could also use slicers for any additional data on your fact table.  Is this in line with what you're looking for?

Hi Anonymous

Here is a DAX based solution that might be handy to build on.  Just create a new table

Summary Table = SUMMARIZE(
    FILTER(
         CROSSJOIN('LogData','Time'),
         [Hour] >= [Start Time] 
         && [Hour] <= [End Time]),
         [Hour],
         "Count",COUNTROWS('LogData')
         )