Busy times for reservaitons

[Anonymous]

Hello to all,

I have the start and end time ranges of reservations. Based on these, I want to calculate how many reservations are made in 1-hour intervals in 24 hours. If the reservation is for two hours, I want to calculate it in the interval of two hours. For example, I have a reservation between 9 PM and 11 PM, I want to add this one 9-10 PM and 10-11 PM slots. Is it possible to calculate this way? Thank you for your help. 

[SpartaBI · Accepted solution]

@Anonymous I'm attaching the file with the solution: Busy times for reservaitons.pbix
I created a parameter time table and this measure:

# Reservations = 
VAR _start_slot = SELECTEDVALUE('Dim Times'[Time Bucket])
VAR _end_slot = _start_slot + TIME(0,0,59) 
VAR _result = 
CALCULATE(
    COUNTROWS('Table'),
    KEEPFILTERS(
        FILTER(
            'Table',
            VAR _start_time = 'Table'[Start_Time - Copy]
            VAR _end_time = IF( 'Table'[End_Time - Copy] <> 0, 'Table'[End_Time - Copy], TIME(23,59,0))
            RETURN
            _start_time <= _start_slot && _end_time >= _end_slot
        )
    )
)
RETURN
    _result

      

Showcase Report – Contoso By SpartaBI

[Anonymous]

Hello, this is amazing. It's work, thanks a lot 🙏

[SpartaBI]

@Anonymous my pleasure 🙂
Hey, check out my showcase report - got some high level stuff there 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂