So does a contract count to July if the contract ends somewhere in the middle? Are there any additional rules?
My suggested approach should work if you are saying 'Nr of contracts at a certain date', then create the table of e.g. Month start dates.
yes, a contract counts to July if it ends somewhere in the middle.
I did look at the INTERVALMATCH and it looks promising. But I actually have 2 date ranges (start/end of contract and start/end of month) and the INTERVALMATCH only checks if a single value is within a range.
Or maybe I don't get it... :-(
I still believe an Intervalmatch should work, but maybe there is an easier solution for you.
Try something like
addmonths(monthstart(startDate),iterno()-1) as ContractMonth
while monthstart(endDate) >= addmonths(monthstart(startDate),iterno()-1);
LOAD * INLINE [
Id , startDate , endDate
1 , 01.01.2011 , 31.12.2011
2 , 01.05.2011 , 31.12.2011
3 , 01.01.2011 , 31.12.2012
4 , 01.05.2011 , 31.12.2012
5 , 20.02.2011 , 1.12.2011
And then create a table with ContractMonth as dimension (or =MonthName(ContractMonth), if you like), and then use