Occupance by datetime

[Anonymous]

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

[lbendlin]

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])

[Anonymous]

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. 

[lbendlin · Accepted solution]

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.

[Anonymous]

Thank you, I have managed to create the table that I would like to have with a filter. Stupid of me to not think about that.

[BA_Pete]

@lbendlin 

Please define CalcuHater.

I'm pretty sure I need this word in my life but must understand the etymology  😄

Pete

[lbendlin]

@BA_Pete 

https://community.powerbi.com/t5/Community-Blog/CALCUHATE-Why-I-Don-t-Use-DAX-s-CALCULATE-Function/b...

[Fowmy]

@Anonymous 

Not clear on the exact question, can you explain more on the requirement?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

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.