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

[wharrison999]

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 Date	End Date	Duration
12/04/2018	03/05/2018	13.00 day(s)
16/03/2018	03/04/2018	11.00 day(s)
06/08/2018	17/08/2018	10.00 day(s)
23/07/2018	03/08/2018	10.00 day(s)

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

	Allocated days	Taken Prior to August	Remaining days of 2018	Booked in August	Booked in September	Booked in October	Booked in November	Booked in December	Total booked	allocation still to be taken in 2018
Name	22	10	12	1	5	3			9	3
Name	22	9	13	1	2			5	8	5

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

[v-juanli-msft]

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]

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

[v-juanli-msft]

Hi @wharrison999

Is your dataset like this

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

[Stachu]

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