Forum Discussion

wbarnold's avatar
wbarnold
New Member
7 years ago

Calculating Pending/Backlog Using a Calendar Table

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?
Need Help
Show and Tell

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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