If these are not DateTime fields, then I would make them so and perform a DateDiff function.  DATEDIFF('day',[subject start],[subject end]).  This should give you the decimal part of the day between the start and end times.

Elias,

You can get the idle time with

WINDOW_SUM( if last() = 0

then

    if DATEDIFF("minute", MIN( [Subject End] ),MIN( DATEADD('hour',2, DATETRUNC('day',[Subject End])) ) ) > 0

    then DATEDIFF("minute", MIN( [Subject End] ), MIN( DATEADD('hour',2, DATETRUNC('day',[Subject End])) ) )

    else 0

    end

else

    if DATEDIFF("minute",MIN(  [Subject End] ), LOOKUP( MIN( [Subject Start] ),1 ) ) > 0

    then DATEDIFF("minute",MIN(  [Subject End] ), LOOKUP( MIN( [Subject Start] ),1 ) )

    else 0

    end

end )

check the computing

So basically,  for the last row (last() = 0)  you do a datediff between  the Subject End  and  2AM  (datetrunc Subject End at the day and add 2 hours).

For the other rows  do a datediff between the Subject End  and  the Subject Start  of the next row  (loockup  with index 1).

Make sure to Sum only the positive difference.

For the second question,  the Time dimension would need to have a  row with a value at 2am if you want to display the Time as a discrete value.

If you are willing to change your view a bit (sheet Example(7)) , you can put Hour(Time)  as a continuous axis on the column. You can set the tick marks of the axis at the Hour,  and format the Date with  HH:mm.

Next you create a calculation  DATEADD('hour',2, DATETRUNC('day',[Subject End])) and you bring it on the Detail shelf. From there you create an invisible reference line using this calculation.  This will add the  02:00  hour  on the axis.

Michel

Elias,

The goal will be to restart the calculation on every Classroom Date  instead of every Classroom code.

I made some modification on the idleTime calculation to take into account the different class hours range.

I am not sure it covers all the different cases but at least it gives you an idea on how to apply the logic for this type of problem.

Pay attention , idleTime is now a nested calculation composed of two calculations and you have to set the computing in both.

Also , since you may want to display more than one classroom, with dates spanning on multiple days, your Hour of Time axis will spread on more than one day. You may want to go with  Example(8) where the Time(hour) is independant of the Date.

I will be out of the office for the next two weeks, so I might not be able to get back to you for a while if you have other questions.