Forum Discussion
Help needed - create metric based on 2 datasets. Use one total form DS1 and use 2 columns from DS2
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
7 Replies
- lbendlin
Super User
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-Forum/ba-p/963216
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/1447523Hi 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 countedDate 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.
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.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
