Help needed - create metric based on 2 datasets. Use one total form DS1 and use 2 columns from DS2

[christiaanvr]

Hi there, I am not getting it so I ask for help.

I have 2 datasets. One is a dataset with room checkins on each day. So I can pick the TOTAL of one Day. So I have TOTAL GUEST IN for e.g. 28-03-2024.

I have a dataset from a people counter at our entrance containing PEOPLE ENTERING and EXIT hotel per 15 minutes timeframe.

Now I want to PLOT form starting 03:00 28-03-2024 number of TOTAL GUEST IN coming from DS1, and then use the TOTAL to calculate # of peopel COME IN and GO OUT on top of the TOTAL GUEST IN, to present on graph each 15 minutes between 03:00 AM en 02:59 (24 hours rolling window) how many people are INSIDE the hotel.

How to get this calculated and work in PowerBI?

Thanks for the help

[christiaanvr]

And we want the time frame between 03:00-02:59 instead of 00:00-23:59 for a complete day. So we do'nt start with occupancy at 00:00 but at 03:00

[lbendlin]

Please provide sample data that fully covers your issue. Multiple days of data.
Please show the expected outcome based on the sample data you provided.

[christiaanvr]

Hi sir, many thanks for your effort. I put 2 spreadsheets online - I can't share that much data in this post https://drive.google.com/drive/folders/1-EY_l80W4QGKE90E-EYYPjtiJv_hUXz-?usp=drive_link

For the output, this is what I would like to present myself:

- red line is the OCCUPANCY based on ROOM PRESENCE from 03:00 and then use IN & OUT count on top of the ROOM OCCUPANCY to show real occupancy in hotel

THe purple and yellow bars shos the IN & OUT count in same graph

The tbael below I want to show the hourly heatmap of occuapncy per hour that day

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[christiaanvr]

Hi there @lbendlin thanks for your response.

I have added the datasets, for one 2 hours ( every 15 mins the IN and OUT coutn of people) below, and the other data set for 8 rooms the checkin's showing the number of people.

So we need to SUM UP the number of persons in the rooms, that's our starting point at 03:00 AM each day. So that represents the occupancy in the hotel. Then Use that number of persons to INCREASE or DECREASE by the number of people ENTERING THE HOTEL (IN) or LEAVE the HOTEL (OUT), and this keeps calcultaitng during the day until 02:59. Then It needs to LOOK UP the # of people in ROOMS on the next day, and then INCREASE/DEcREASE by # of IN and OUT. 

Representation = see screenshot.

Visitors counted

Date	IN	OUT
mrt 31, 2024 07:00	1	3
mrt 31, 2024 07:15	2	2
mrt 31, 2024 07:30	2	3
mrt 31, 2024 07:45	0	0
mrt 31, 2024 08:00	2	2
mrt 31, 2024 08:15	2	10
mrt 31, 2024 08:30	0	0
mrt 31, 2024 08:45	2	7

Rooms occupied

Date	Room #	# of persons
31-03-2024	301	2
31-03-2024	302	1
31-03-2024	303	1
31-03-2024	304	2
31-03-2024	305	2
31-03-2024	306	2
31-03-2024	307	1
31-03-2024	308	2

[lbendlin]

I would rather do that as a waterfall chart.

[christiaanvr]

@lbendlin thanks for the help - but to reflect your suggestion to show in a waterfall chart , in that type of graph I don't see the total # of people inside the hotel. It only shows me a starting point # of people and then in red/green bar the # of people IN or OUT counted.

I want to see the total number of people as a number in a graph over time for all days in 15min. So this is not really what I am looking for. Like the example screenshot.. the yellow line I would like to represent:

- hotel rooms guests (number) Plus and MINUS the IN & OUT counts during the dya. So taht yellow line wil show rolling total over the day

- the green/blue bar shows the IN & out counts to present me peak times for visitors