Split Actual leave days booked in to months based on Start and End Dates

[rush]

Hi

I have a leave table that has actual leave days booked which I would like to split them out into months that were taken when they overlap based on their Start and End Dates.

I have a date table that has a column working out workdays "Is a Work Day (Excl. Public Holidays & Weekends)". The date table is joined to the Leave table via Start Date.

Expected Result 

Please assist.

Thank you very much in advance.

e.g.

EMPLOYEE	LEAVE TYPE	LEAVE PERIOD	DATE REQUESTED	START DATE	END DATE	ACTUAL DAYS LEAVE	Dec Leave	Jan 2020 Leave	LEAVE STATUS
Staff 1	Annual	Full Day	2019/12/01	2019/12/17	2020/01/14	18	9	9	Approved

[amitchandak]

Please look at my blog on HR, this about employee duration, but the same can be used in your case. You can to count dates from date dimension instead of employee id in current/Active employee calculation

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[rush]

  Thank you very much for the info but I can't seem to get it right.

I have created my relationships as stated (1 active and 1 inactive) and created the measure needed to count the dates but it is giving me back syntax errors, please see below: 

No. of days taken = CALCULATE(COUNTx(FILTER(v_LEAVE, v_LEAVE[StartDate]<=max('Dim_Date'[Calendar_Date]) && (ISBLANK( v_LEAVE[EndDate]) || v_LEAVE[EndDate]>max('Dim_Date'[Calendar_Date]))) ,
('Dim_Date'[Calendar_Date])) , v_LEAVE[StartDate])), CROSSFILTER(v_LEAVE[StartDate],'Dim_Date'[Calendar_Date],None))

@amitchandak

[amitchandak]

Try like

No. of days taken = CALCULATE(COUNTx(FILTER(v_LEAVE, v_LEAVE[StartDate]<=max('Dim_Date'[Calendar_Date]) 
				&& (ISBLANK( v_LEAVE[EndDate]) || v_LEAVE[EndDate]>max('Dim_Date'[Calendar_Date]))) ,
 , v_LEAVE[StartDate]), CROSSFILTER(v_LEAVE[StartDate],'Dim_Date'[Calendar_Date],None))

[rush]

@amitchandakThanks for the correction but it brings up blank values based on that formula.

[amitchandak]

Can you share sample data and sample output.  I will try.

[rush]

@amitchandakThank you very much.

Please see link to excel file with sample data and expected output.

This will have to be a measure to cater for the various months when comparing.

Sample Data 

[amitchandak]

The formula will be like this

Measure = CALCULATE(sumx(SUMMARIZE(filter(CROSSJOIN(Table2,Dim_Date),Dim_Date[Calendar_Date] >= Table2[StartDate] && Dim_Date[Calendar_Date]<= Table2[EndDate]),Dim_Date[Calendar_Date],Dim_Date[Is a Work Day (Excl. Public Holidays & Weekends)]),Dim_Date[Is a Work Day (Excl. Public Holidays & Weekends)]))

Check solution at :https://www.dropbox.com/s/1c3eg4y52d49uel/Leave%20test%20data.pbix?dl=0

In case this not working, let me know

[rush]

@amitchandakThan you but it is only working out for the Start Date and not the End Date.

Example. Staff01 books leave for the dates below which the measure calculates correctly only for the Start Date (Nov-2019) and not the End Date (Dec-2019).

[amitchandak · Accepted solution]

Check the file at: https://www.dropbox.com/s/1c3eg4y52d49uel/Leave%20test%20data.pbix?dl=0

Measure = CALCULATE(sumx(SUMMARIZE(filter(CROSSJOIN(Table2,Dim_Date),Dim_Date[Calendar_Date] >= Table2[StartDate] && Dim_Date[Calendar_Date]<= Table2[EndDate]),Dim_Date[Calendar_Date],Dim_Date[Is a Work Day (Excl. Public Holidays & Weekends)]),Dim_Date[Is a Work Day (Excl. Public Holidays & Weekends)]),CROSSFILTER(Dim_Date[Calendar_Date],Table2[StartDate],None))

[rush]

@amitchandakThank you very much but for some odd reason it is not bringing back the results in my live dataset.

My DAX query:

No. of days taken = 

CALCULATE(sumx(SUMMARIZE(filter(CROSSJOIN(v_LEAVE,Dim_Date),Dim_Date[Calendar_Date] >= v_LEAVE[StartDate] && Dim_Date[Calendar_Date]<= v_LEAVE[EndDate]),Dim_Date[Calendar_Date],Dim_Date[Is a Work Day (Excl. Public Holidays & Weekends)]),Dim_Date[Is a Work Day (Excl. Public Holidays & Weekends)]),CROSSFILTER(Dim_Date[Calendar_Date],v_LEAVE[StartDate],None))

[amitchandak]

Check the Active join. In my case start date join was Active. Also, I made End date join 1 to Many. But later should not have an impact.

[rush]

@amitchandakThere was another relationship that my leave table was joined to (being my Dim_Staff table) which caused it to not display the results.

Thank you very much for the solution.

Have a fantastic day.