cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wharrison999 Frequent Visitor
Frequent Visitor

Holidays - Calculate number of days taken/to take each month with Start and End Dates booked

Hi Everyone,

 

I am new to Power BI and this community.

 

I wonder if any of you lovely people could help me please.

 

I need to calculate how many holidays have been taken & booked throughout the year showing the number of days by Month.

 

Here is a snapshot of data

 

Start DateEnd DateDuration
12/04/201803/05/201813.00 day(s)
16/03/201803/04/201811.00 day(s)
06/08/201817/08/201810.00 day(s)
23/07/201803/08/201810.00 day(s)

 

and this is an example of what I am trying to achieve.

 

 Allocated daysTaken Prior to AugustRemaining days of 2018Booked in August Booked in September Booked in OctoberBooked in November Booked in December Total bookedallocation still to be taken in 2018
Name221012153  93
Name2291312  585

 

The snapshot shows I have holidays booked over two month periods.  I need for the duration of days to be split by each of the months to enable me to produce my report.

 

I have a calendar attached to the report.

 

Many Thanks

Kind Regards

Wendy

 

4 REPLIES 4
Community Support Team
Community Support Team

Re: Holidays - Calculate number of days taken/to take each month with Start and End Dates booked

Hi @wharrison999

From your information, I;m not clear how to get the table (the second picture) from table1(the first picture).

Could you tell me more about it?

 

Best Regards

Maggie

wharrison999 Frequent Visitor
Frequent Visitor

Re: Holidays - Calculate number of days taken/to take each month with Start and End Dates booked

Hi,

 

Sorry I probably complicated it a little with the table I am trying to achieve.

 

Basically what I want to be able to do is if a holiday falls over a 2 month period I need to know how many days in one month and how many days in the other month.

 

For Example: 

Someone books a holiday from 24/08/18 to 10/09/18.  Taking weekends and the Bank Holiday into consideration they are actually taking 5 days in August and 5 days in September.

 

I need Power BI to work out how many actual working days have been taken in which month.

 

I do have a calendar attached to my report.

 

I hope this makes more sense.

 

Kind Regards

Wendy

 

 

Super User
Super User

Re: Holidays - Calculate number of days taken/to take each month with Start and End Dates booked

you will need a working day marker in your Calendar table (e.g. with 1 for each working day and 0 for bank holidays)
Then for a measure e.g. [Booked in August] you need to count days between start & end that are in August & are working days

Community Support Team
Community Support Team

Re: Holidays - Calculate number of days taken/to take each month with Start and End Dates booked

Hi @wharrison999

Is your dataset like this

10.png

If I exclude the weekend and start date, end date from the period, i still can't get the correct duration as your table shown.

What is the "the Bank Holiday" ?

 

Best Regards

Maggie