Calculating occupancy at specific times

[alzhf]

I've got a table with occupancy data and I need to calculate the occupancy at specific times. The table looks something like this

in	out
01.01.2020 01:00	01.01.2020 23:00
01.01.2020 02:00	03.01.2020 12:00
02.01.2020 07:00	02.01.2020 23:00

What I now want is a new table where I can see the occupancy at every day of the year, e.g.

date	occ 06:00	occ 22:00
01.01.2020	2	2
02.01.2020	1	2
03.01.2020	1	1

I most likely need a date-table for this, but I don't really know how to approach this problem in detail, so every help or idea is greatly appreciated.

[ImkeF]

Hi @alzhf ,
you should create one large table with one row for each interval you want to observe. 
Then create a calendar table and a time table separately. So make sure that your large table has date and time in different columns.
Your desired result table would then be a matrix with a simple count-measure in it (COUNTROWS(FactTable)).
If you need help building this large table, please let me know if you need anything else than 6:00 and 22:00 occupancies.

[alzhf]

Hey Imke,

I solved it by using a simple COUNTROWS, just as you suggested. 
I created a Date Table with a custom date format and inserted a calcuated column with this code.

occupancy = COUNTROWS(
    FILTER(
        TABLE,AND(
            TABLE[IN DATE]<=DATETABLE[DATE],
            TABLE[OUT DATE]>=DATETABLE[DATE])))

This works as intended.

Do you know a way to extract the IDs for the correlating time stamps? Right now, I can only count the specific IDs/lines from the table, but I don't have access to the specific IDs.

Maybe by unpivoting the Date table and listing the specific IDs under the dates? 

I'm currently trying to do this with this code:

CM eff Midnight = 
var _rangeStart= MIN('Date Slicer'[Date])
var _rangeEnd= MAX('Date Slicer'[Date])
return
CALCULATE( 
    SUMX(Table2,Table2[Value]),((Table1[DATE in]) >= _rangeStart),((Table1[DATE out]) <= _rangeEnd))

but I'm not getting the correct result. Do you see my mistake here?