topic Re: Calculating occupancy at specific times in Desktop

Calculating occupancy at specific times

alzhf — Fri, 14 Oct 2022 09:40:51 GMT

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.

Re: Calculating occupancy at specific times

ImkeF — Fri, 14 Oct 2022 14:31:28 GMT

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.

Re: Calculating occupancy at specific times

alzhf — Sun, 16 Oct 2022 22:24:16 GMT

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?