How to Calculate Number os Stores that opens in a certain Period

[Anonymous]

Hello Everyone!

In my organization, the sales area must bring a lot of stores to our plataform that is a marketplace. (same idea as UberEats)

I would like to know, above all the stores that we have in a certain area, how many open stores we have in the morning, afternoon, at night in every day of the week.

In my database we have this columns:

Store_ID, Time_Open, Time_Closed, Day

So, just to give an example, if I have a Store_ID that has a Time_Open = 09 and Time_Closed 13, on monday.

He should appear open at 09,10,11,12,13 on monday. So the time range is very important here.

Just a important detail, the Time_Open and Time_Closed both are whole number type.

I thought about something like:

Monday -> Morning (5 Stores open), Afternoon (10 Stores open), Night (20 Stores open)

Tuesday -> The same thing.

And do this to every day of the week.

Thank you very much in advance guys!!

Have a good day

[FrankAT]

Hi @Anonymous ,

here is my sample. Take a look at the attached PBIX-file.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

Thank you so much!!

You are a life saver!!

Best Regards

[Anonymous]

That solution is outstanding!! But I only missed the day part. Every store has a different open time and close time for day. So it is important to add this variable (monday, tuesday, wednesday, and all)

It is possible to add the day variable, so we can check as a filter?

[FrankAT · Accepted solution]

Hi @Anonymous ,

I attached the possibility to select the weekday and date with a hierachy slicer. Take a look at the new PBIX-file:

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[TomMartens]

Hey @Anonymous ,

Please consider to create a pbix file that contains sample data, but still reflects the data model (https://docs.microsoft.com/en-us/learn/modules/design-model-power-bi/).
You can use this to enter data directly in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enter-data-directly-into-desktop. Upload the pbix file to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.
Create columns that contain the expected results, use the sample data to explain how the result is derived from the sample data.

Regards,
Tom