Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
christiaanvr
New Member

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 7
christiaanvr
New Member

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

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.

 

lbendlin_0-1712178548943.png

 

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

 

Scherm­afbeelding 2024-04-04 om 08.52.48.png

lbendlin
Super User
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-...

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...

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

DateINOUT
mrt 31, 2024 07:0013
mrt 31, 2024 07:1522
mrt 31, 2024 07:3023
mrt 31, 2024 07:4500
mrt 31, 2024 08:0022
mrt 31, 2024 08:15210
mrt 31, 2024 08:3000
mrt 31, 2024 08:4527

 

Rooms occupied

DateRoom ## of persons
31-03-20243012
31-03-20243021
31-03-20243031
31-03-20243042
31-03-20243052
31-03-20243062
31-03-20243071
31-03-20243082

 

Scherm­afbeelding 2024-04-02 om 10.30.43.png

I would rather do that as a waterfall chart.

lbendlin_0-1712087760458.png

 

 

@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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.