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?
