No of ids in a date range. Ids fall under range to be calculated in each row

[bharukc]

Below is the sample table, I need to calculate

count of ids that occured between 1/1/2012-1/1/2013, 4/1/2012-4/1/2013, 7/1/2012-7/1/2013 and so on. How many Ids were due from the start of the quarter to a year from the start of quarter. IDs that fall under due between 1/1/2012-1/1/2013 will also be in 4/1/2012-4/1/2013, as they share common dates. 

Id	due date
1	1/3/2012
2	5/3/2012
3	8/3/2012
4	9/2/2012
5	1/4/2013
6	3/5/2013
7	4/9/2013
8	8/9/2013
9	9/10/2013

there will be overlapping time among these dates. I need to be able to count the id that fall under each date range multiple times. For eg, there might be 6 ids that fall under 1/1/2012-1/1/2013 range and 8 ids fall under 4/1/12-4/1/13. Out of these 8, 4 would fall under both 1/1/2012-1/1/2013 and 4/1/12-4/1/13 range. I need to be able to count ids that fall under that date range regardless if they are being counted on previous rows.

[Anonymous · Accepted solution]

Hi @bharukc ,

Based on your description, I have firstly customed a calendar table:

Then use STARTOFQUARTER() to get all this year and next year 's  "the start of the quarter to a year" 

New Table =
ADDCOLUMNS (
    DISTINCT (
        SELECTCOLUMNS ( 'Dates', "start", STARTOFQUARTER ( 'Dates'[Date] ) )
    ),
    "end", DATE ( YEAR ( [start] ) + 1, MONTH ( [start] ), DAY ( [start] ) )
)

Now we could count rows in each period:

Count = CALCULATE(COUNTROWS('Table'),FILTER('Table',[due date]>=MAX('New Table'[start]) && [due date]<=MAX('New Table'[end])))

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.