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

June 2015
July 2015
August 2015
September 2015

I am looking for two types of Output  (high level and 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.



July 28th, 2015 11:39am

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

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


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   


July 29th, 2015 3:34am

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

Other recent topics Other recent topics