Bookings in working hours

[Anonymous]

Hi,

I have a bookings report, and I need to work out core minutes booked, which needs to follow within the hours of 08:00 - 18:00.

If the start date is on a weekend/bank holiday it needs to be pushed forward to the next working day, and if the end date is, then this needs to be pushed back to the previous working day.

If the start time or end time is < 8:00 then set to 8:00, or if it is >= 18:00 then set to 18:00.

The final criteria is if it is a block booking or a long booking, this needs to exclude counting weekends and bank holidays.

I have my date table set up with working days and bank holidays. I was just wondering if something like this was possible, as Im struggling to calculate core minuted booked, and be able to filter by the date?

Thanks in advance

Ive attached a sample file with a block booking and 3 day booking.

https://1drv.ms/u/s!AtcnGX-0tS5riSNuw1dleU5wNQxe?e=7WwaCP

[ryan_mayu]

@Anonymous 

please try to create a column

Column = 
VAR _startdate=DATEVALUE('Bookings'[Start Date and Time])
VAR datetype= LOOKUPVALUE('vwDimDate'[WorkingDayFlag],'vwDimDate'[Date],_startdate)
VAR _finalstartdate=if(datetype=0,MINX(FILTER('vwDimDate','vwDimDate'[Date]>_startdate && vwDimDate[WorkingDayFlag]=1),'vwDimDate'[Date]),_startdate)
VAR _starttime=TIMEVALUE(Bookings[Start Date and Time])
VAR _finalstarttime=if(_starttime<TIME(8,0,0),time(8,0,0),if(_starttime>TIME(18,0,0),TIME(18,0,0),_starttime))
VAR _finalstartdatetime=DATEVALUE(_finalstartdate)+TIMEVALUE(_finalstarttime)
VAR _enddate=DATEVALUE('Bookings'[End Date and Time])
VAR datetype2= LOOKUPVALUE('vwDimDate'[WorkingDayFlag],'vwDimDate'[Date],_enddate)
VAR _finalenddate=if(datetype2=0,MAXX(FILTER('vwDimDate','vwDimDate'[Date]<_startdate && vwDimDate[WorkingDayFlag]=1),'vwDimDate'[Date]),_enddate)
var _endtime=TIMEVALUE(Bookings[End Date and Time])
VAR _finalendtime=if(_endtime<TIME(8,0,0),time(8,0,0),if(_endtime>TIME(18,0,0),TIME(18,0,0),_endtime))
VAR _finalenddatetime=DATEVALUE(_finalenddate)+TIMEVALUE(_finalendtime)
return (_finalenddatetime-_finalstartdatetime)*24*60

However I have a question.

2020/6/1 and 6/3 are both working days. There are 2days and 8 hours from  6/1 9am to 6/3 5pm , totally 56 hours, so 56*60 is 3360 mins, I am not sure why your expected result is 1440 mins.

The result is negative in the last row because you don't have the date date of year 2028 in your date table.

Hope this is helpful.

[Anonymous]

Hi @ryan_mayu ,

Many thanks for your reply. Regarding the 2020/6/1 and 6/3. It is a 3 day booking with core hours of 09:00 - 17:00.

This would mean 3 x 8 hours, * 60 to get core minutes. If the booking is a block booking then it needs to automatically account for 08:00 - 18:00.

I hope that makes sense,

Thanks

Liam

[ryan_mayu · Accepted solution]

@Anonymous 

please try this

Column = 
VAR _startdate=DATEVALUE('Bookings'[Start Date and Time])
VAR datetype= LOOKUPVALUE('vwDimDate'[WorkingDayFlag],'vwDimDate'[Date],_startdate)
VAR _finalstartdate=if(datetype=0,MINX(FILTER('vwDimDate','vwDimDate'[Date]>_startdate && vwDimDate[WorkingDayFlag]=1),'vwDimDate'[Date]),_startdate)
VAR _starttime=TIMEVALUE(Bookings[Start Date and Time])
VAR _finalstarttime=if(_starttime<TIME(8,0,0),time(8,0,0),if(_starttime>TIME(18,0,0),TIME(18,0,0),_starttime))
VAR _finalstartdatetime=DATEVALUE(_finalstartdate)+TIMEVALUE(_finalstarttime)
VAR _enddate=DATEVALUE('Bookings'[End Date and Time])
VAR datetype2= LOOKUPVALUE('vwDimDate'[WorkingDayFlag],'vwDimDate'[Date],_enddate)
VAR _finalenddate=if(datetype2=0,MAXX(FILTER('vwDimDate','vwDimDate'[Date]<_startdate && vwDimDate[WorkingDayFlag]=1),'vwDimDate'[Date]),_enddate)
var _endtime=TIMEVALUE(Bookings[End Date and Time])
VAR _finalendtime=if(_endtime<TIME(8,0,0),time(8,0,0),if(_endtime>TIME(18,0,0),TIME(18,0,0),_endtime))
VAR _finalenddatetime=DATEVALUE(_finalenddate)+TIMEVALUE(_finalendtime)
VAR _days=abs(_finalenddate-_finalstartdate)
return if(_days<2,(_finalenddatetime-_finalstartdatetime)*24*60,(_days-1)*8*60+((_finalendtime-time(9,0,0))+(time(17,0,0)-_starttime))*24*60)

For last row, since I can't change your vwdatetime table, please add date to the end of 2028/12/31. Otherwise, can't calculate the correct minutes.

What's more ,you need to futher improving the DAX to check if this is a block booking.

[Anonymous]

Hi @ryan_mayu ,

Ive added in up to 2028 in my date table, but it still appears to be minus in mins booked.

I have 10 bookable hours for each day essentially, and I'm just trying to compare booked against bookable but I'm really struggling, especially with getting each booking rounded up.

https://1drv.ms/u/s!AtcnGX-0tS5riSQobqjconXiyMs8?e=0j6tAP

Thanks

Liam

[ryan_mayu]

@Anonymous 

Sry about that, something wrong with the coding and i will try to fix this. However, still have a question.

for last row, startdate should be 2016/1/4(since 1/1 is not workingday), enddate should be 2028/12/29(since 12/31 is not workingday)

the days between these two dates are 4743. As you said, if it's a block blocking, we need to calculate the time from 8am to 6pm, which are 10 hours per day. My result it different from yours. Could you please explain how you get the mins for last row again?

[Anonymous]

Hi @ryan_mayu ,

Many thanks again for getting back to me. I believe that would be total days difference 4743.

Ideally it needs to be 08:00 - 18:00 working days only and rounded up.

Mine was calculated by a function in SQL which removes the bank holidays and weekends. Ive just checked and the bank holidays only go up to 2021, so this isnt fully accurate so you can ignore my result.

It seems there is around 3313 business days between. If its easier to shorten the dates to ensure accuracy, this is fine.

Thanks again

Liam

[ryan_mayu]

@Anonymous 

please try this

Mins Booked = 
VAR _startdate=DATEVALUE('Bookings'[Start Date and Time])
VAR datetype= LOOKUPVALUE('vwDimDate'[WorkingDayFlag],'vwDimDate'[Date],_startdate)
VAR _finalstartdate=if(datetype=0,MINX(FILTER('vwDimDate','vwDimDate'[Date]>_startdate && vwDimDate[WorkingDayFlag]=1),'vwDimDate'[Date]),_startdate)
VAR _starttime=TIMEVALUE(Bookings[Start Date and Time])
VAR _finalstarttime=if(_starttime<TIME(8,0,0),time(8,0,0),if(_starttime>TIME(18,0,0),TIME(18,0,0),_starttime))
VAR _finalstartdatetime=DATEVALUE(_finalstartdate)+TIMEVALUE(_finalstarttime)
VAR _enddate=DATEVALUE('Bookings'[End Date and Time])
VAR datetype2= LOOKUPVALUE('vwDimDate'[WorkingDayFlag],'vwDimDate'[Date],_enddate)
VAR _finalenddate=if(datetype2=0,MAXX(FILTER('vwDimDate','vwDimDate'[Date]<_enddate && vwDimDate[WorkingDayFlag]=1),'vwDimDate'[Date]),_enddate)
var _endtime=TIMEVALUE(Bookings[End Date and Time])
VAR _finalendtime=if(_endtime<TIME(8,0,0),time(8,0,0),if(_endtime>TIME(18,0,0),TIME(18,0,0),_endtime))
VAR _finalenddatetime=DATEVALUE(_finalenddate)+TIMEVALUE(_finalendtime)
VAR _days=CALCULATE(COUNTROWS(vwDimDate),FILTER(vwDimDate,'vwDimDate'[Date]>=_finalstartdate&&'vwDimDate'[Date]<=_finalenddate&&vwDimDate[WorkingDayFlag]=1))
VAR _type=LEFT(Bookings[Booking],5)
return if(_type="Block",_days*10*60,if(_days<2,(_finalenddatetime-_finalstartdatetime)*24*60,(_days-2)*8*60+((_finalendtime-time(9,0,0))+(time(17,0,0)-_starttime))*24*60))

[Anonymous]

thankyou @ryan_mayu 🙂

[Anonymous]

Thankyou 🙂 @ryan_mayu