cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Bookings in working hours

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resident Rockstar
Resident Rockstar

Re: Bookings in working hours

@lherbert502 

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)

1.PNG

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.

 

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

9 REPLIES 9
Highlighted
Resident Rockstar
Resident Rockstar

Re: Bookings in working hours

@lherbert502 

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

1.PNG

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.

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan
Highlighted
Post Prodigy
Post Prodigy

Re: Bookings in working hours

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

Highlighted
Resident Rockstar
Resident Rockstar

Re: Bookings in working hours

@lherbert502 

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)

1.PNG

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.

 

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

Highlighted
Post Prodigy
Post Prodigy

Re: Bookings in working hours

Thankyou 🙂 @ryan_mayu 

Highlighted
Post Prodigy
Post Prodigy

Re: Bookings in working hours

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.

 

111111111-07-07 145358.jpg

 

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

 

Thanks

 

Liam

Highlighted
Resident Rockstar
Resident Rockstar

Re: Bookings in working hours

@lherbert502 

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?

1.PNG

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan
Highlighted
Post Prodigy
Post Prodigy

Re: Bookings in working hours

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

Highlighted
Resident Rockstar
Resident Rockstar

Re: Bookings in working hours

@lherbert502 

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

1.PNG

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan
Highlighted
Post Prodigy
Post Prodigy

Re: Bookings in working hours

thankyou @ryan_mayu 🙂

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors