Calculating Pending/Backlog Using a Calendar Table

[wbarnold]

I am trying to calculate the backlog in our company based on a calendar table.  I have two tables, one is a calendar table and the second table contains the milestone dates and unique identifer for each request ('vWBA-Milestones).  Using import, I've managed to calculate the backlog (incomplete) for each date in a year using the calendar table, based on the date that each request was received and the date the work was released.  

Pending (All) = CALCULATE(

DISTINCTCOUNT('vWBA-Milestones'[RequestId]),

FILTER('vWBA-Milestones','vWBA-Milestones'[RequestDate Clean]<=MIN(vWBA_BI_Calendar[Calendar_Date]) && 'vWBA-Milestones'[ReleasedDate Clean]> Max(vWBA_BI_Calendar[Calendar_Date])))

I need to do the same thing using direct query.   The problem is that there are too many records and SQL throws out an error stating I have exceeded a 64,000 threshold.  I created another calendar table that only includes the first day of the week.  No errors, but I also get back no data.  Each date in the calendar table may or may not have a milestone date that matches in the milestone table.  Does anyone know how to do this or have a suggestion I could try?

[Anonymous]

Hi @wbarnold ,

In my opinion, I'd like to suggest you add more condition to grouping records based on current category fields or add more filter to limit calculation records range.

Regards,

Xiaoxin Sheng