Forum Discussion
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 |
| 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.
2 Replies
- ImkeF
Community Champion
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.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?
