Forum Discussion
Occupance by datetime
Hi all,
I am trying to create a calculation based on the amount of persons that are in a room based on two datetime field. I have a date table and another table that looks like this:
| ID | In | Out | dateFrom | dateTo | End of Hour |
| 1 | 5 | 0 | 8/18/2020 10:00:00 AM | 8/18/2020 10:15:00 AM | 11 |
| 2 | 10 | 7 | 8/18/2020 10:15:00 AM | 8/18/2020 10:30:00 AM | 11 |
| 3 | 5 | 10 | 8/18/2020 10:30:00 AM | 8/18/2020 10:45:00 AM | 11 |
| 4 | 42 | 40 | 8/18/2020 10:45:00 AM | 8/18/2020 11:00:00 AM | 12 |
| 5 | 23 | 28 | 8/18/2020 11:00:00 AM | 8/18/2020 11:15:00 AM | 12 |
I already created 2 simple measure to calculate the total In and Out with a SUM(). With the third measure I am trying to calculate the difference between them. I now have a simple subtraction of both measure.
When I throw this measure into an table, combine this with the date of my date table and the End of Hour column, created in Power Query, I only get to see the difference between the hours. But I also need to see in the next hour how many persons there are still in the room of the last hour. This makes sense because in my calculation I do not take the dateFrom and dateTo columns.
My question is, I know that I have to use them to calculate the exact difference between these columns and also to remember the amount of persons of the last hour. Only I do not know how I can take these columns into the measure. I would like to create the following table:
| ID | In | Out | dateFrom | dateTo | End of Hour | Occupancy |
| 1 | 5 | 0 | 8/18/2020 10:00:00 AM | 8/18/2020 10:15:00 AM | 11 | 5 |
| 2 | 10 | 7 | 8/18/2020 10:15:00 AM | 8/18/2020 10:30:00 AM | 11 | 8 |
| 3 | 5 | 10 | 8/18/2020 10:30:00 AM | 8/18/2020 10:45:00 AM | 11 | 3 |
| 4 | 42 | 40 | 8/18/2020 10:45:00 AM | 8/18/2020 11:00:00 AM | 12 | 5 |
| 5 | 23 | 28 | 8/18/2020 11:00:00 AM | 8/18/2020 11:15:00 AM | 12 | 0 |
Thank you in advance
When you create measures you have to think about filter context and the type of visual you want to use. A good measure works in one scenario. A great measure works in multiple scenarios, including Totals.
8 Replies
Hi Fowmy , thank you for your reply.
I would like to calculate the occupancy of a room based on the hour on a daily basis. I have created an image, where I have put the dataset in it that I have and the table that I want to create, with the hope that my question become a little bit more clearly.
My goal is to calculate the difference of the IN and OUT numbers, based on the datetime so I can create a line graph with it or an Matrix.With this Image, is my question a little bit more clearly? Otherwise, please explain what part you don't understand and therefor I can try to explain that part a bit more. Sorry, but my english is not really good.
Occupancy := var i = max(Occupancy[ID]) return CALCULATE(sum(Occupancy[In])-sum(Occupancy[Out]),allselected(Occupancy),Occupancy[ID]<=i)or for the CalcuHaters:
Occupancy := sumx(FILTER(allselected(Occupancy),Occupancy[ID]<=max(Occupancy[ID])),Occupancy[In]-Occupancy[Out])Hi lbendlin ,
I don't need to filter on the ID, I use a slicer for this. I need to filter on de time, please take a look at my picture that I have posted in the previous post. I want to recreate the right table, in the image, with the data that is on the left.When you create measures you have to think about filter context and the type of visual you want to use. A good measure works in one scenario. A great measure works in multiple scenarios, including Totals.
