Calculate seated guests in restaurant based on arrival time and departure

[aschiettekatte]

My goal is to build a visual of number of guests (y-axis) seated in a restauarant, per half hour (x-axis).

My data set contains of rows which refer to a reservation with

- a column with arrival date and time (CreateDT)

- a column with departure date and time (BillDT)

- number of guests (Covers).

The arrival times should not be rounded by the half hour. They are only to be counted when present on the specific half hour between BillDT and CreateDT

Preferably the visual should colors for each section of the restaurant (four in total), being column (Name).

How should I go about this?

My data set:

Name	CreateDT	BillDT	RunningTimeInMinutes	TableNr	TotalPrice	TotalQuantity	Covers
Restaurant	13-2-2021 20:05	13-2-2021 20:05	0	1	12,5	3	3

[v-xiaotang]

Hi @aschiettekatte 

Could you share some sample data in text and the expected result? Thanks 🙂

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[aschiettekatte]

Thank you very much for reaching out! Very much appreciated!

So My first table consists of rows for each restaurant reservation.

Columns are:

* date/time for start time.

* date/time for the end time

* number of persons

Expacted output is time on the x-axis and persons on the y-axis.

Preferably bar chart, bar per half hour.

(No rounding for the reservation times is required, only count persons if they are 'in' on the half hour.)

Furthermore filter options per ISO week and weekday.

Does this make sense?

Thx again!

[v-xiaotang · Accepted solution]

Hi @aschiettekatte 

I create a sample for your reference,  is the result similar to what you want?

measure:

CountPerHalfHour = 
var _axistime=FORMAT(MIN('Hour'[Hour]),"hh:mm:ss")
var _count= CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),FORMAT('Table'[start time],"hh:mm:ss")<=_axistime&&FORMAT('Table'[end time],"hh:mm:ss")>=_axistime))
return _count

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Timmit]

Hi,

Your measure works perfectly for my case as well, thank you very much for sharing it here. 

I have one question though, maybe you'll be able to help me with it? When a specific start time is not listed in the table of visits/reservations, it doesn't show up in any visual, even though guests who were placed before that start time are still present. 

As an example in the screenshot below, the start times 18:00 and 18:15 are represented, but because not a single visit started between 18:30 and 19:00, those times are not visible in the graph, even though the visitors who started at 18:00/18:15 are still present.

How would I go about including those times in the visuals as well?

Thank you very much in advance!

[aschiettekatte]

Dear Team_Tang,

Very much so.

One thing though: I would like to be able to add a legend for the restaurant section. Each row contains a column for 'section' of the restaurant, such as Restaurant or Terrace.

Furthermore, my table also contains columns for ISO week and Day of week. I am not able to filter on ISO week or Weekday. 

(Probably noob questions, sorry, but thanks for the help so far!)

[Greg_Deckler]

@aschiettekatte 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[aschiettekatte]

The technique used in the second link (periodic billing) is pretty much what I was hoping for and I was able to build a visual which now displays the guests per month.

Now, how would I go about bringing this principle to the next level of detail, namely guests in house per half hour?