Forum Discussion

alzhf's avatar
alzhf
Frequent Visitor
3 years ago

Calculating occupancy at specific times

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      ...
Need Help
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
alzhf
Frequent Visitor
3 years ago

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?