I am new to PowerBI. I am looking to calculate the monthly holidays by staff member using DAX. I am
able to calculate it  if all entries (Start Date & End Date)  are confined to a unique month. The issue is where a person's holidays go across several months

I have 2 tables

Table 1 - Holidays

Name                        StartDate                                                 EndDate
Joe Bloggs                 27July 2015                                             7th  Aug 2015
Jenny Jones               22nd July 2015                                        23rd July 2015
Jenny Jones               27th  July 2015                                        28th July 2015

Table 2 -Months&Years

Month/Year
June 2015
July 2015
August 2015
September 2015

I am looking for two types of Output  (high level and detailed)

Detailed

Month/Year                        Name                         Total  Days OOO
July 2015                          Joe Bloggs                            5
July 2015                          Jenny Jones                          4
August 2015                      Joe Bloggs                            5

High level

Month/Year                       Days OOO
June 2015                                0
July 2015                                 9
August 2015                             5

Any help would be appreciated.

Thanks

Matt

Hi Matt25L,

First of all, please refer to these articles about Date Calculations in DAX for Microsoft PowerPivot:

https://www.microsoftpressstore.com/articles/article.aspx?p=2228441

http://sqlblog.com/blogs/alberto_ferrari/archive/2011/01/19/working-days-computation-in-powerpivot.aspx

Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

If these articles useless for you, I suggest you post your issue to Power BI forum.

https://social.technet.microsoft.com/Forums/office/en-US/home?category=powerbi

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

Regards,

Emi Zhang
TechNet Community Su