Calcuating Monthly Totals frrm Date Ranges

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

July 28th, 2015 11:39am

You want DATEDIFF(<start date>, <end date>, <interval>)

https://msdn.microsoft.com/en-us/library/dn802538.aspx

So, calculated column for holidays table:

DATEDIFF[StartDate], [EndDate], days)

  • Proposed as answer by Seth Moupre 15 hours 45 minutes ago
Free Windows Admin Tool Kit Click here and download it now
July 28th, 2015 12:04pm

Hi Seth,
This will not work where the dates go form one month to the next

e.g.

Name           StartDate         EndDate
Joe Bloggs    27July 2015        7th Aug 2015  


The output that I am looking for is   

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

Thanks                    

July 29th, 2015 3:34am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics