SUMPRODUCT with COUNTIFS results in #DIV/0!

[AOB]

I have a table of data which includes a timestamp column (date/time)

I need a formula to calculate the average number (count) of rows per week but only up-to and including a given date/time (so not the average across the entire dataset, but the average up to a certain point in the dataset)

I have additional calculated columns [Year] and [Week of Year] derived from the timestamp so that I can identify / group distinct weeks together

This formula works to calculate the average number (count) of rows per week across the entire dataset :

=COUNT(MyTable[Timestamp])/SUMPRODUCT(1/COUNTIFS(MyTable[Year],MyTable[Year],MyTable[Week of Year],MyTable[Week of Year]))

To incorporate the cutoff point, I tried this :

=COUNTIF(MyTable[Timestamp],"<="&[@[Cutoff]])/SUMPRODUCT(1/COUNTIFS(MyTable[Year],MyTable[Year],MyTable[Week of Year],MyTable[Week of Year],MyTable[Timestamp],"<="&[@[Cutoff]]))

But obviously this falls over because the COUNTIFS returns zeros in the array for those records which come after the [Cutoff] date, resulting in the 1/COUNTIFS having division by zero (#DIV/0!)

Any suggestions as to how I can get the average weekly count of records but only focusing on those records up to and including a given date and not the entire dataset?

Thanks!

 

[AOB]

Solved my own problem by eliminating the complexity of the SUMPRODUCT / COUNTIFS and replacing it with something simpler :

=COUNTIF(MyTable[Timestamp],"<="&[@[Cutoff]])/ROUNDUP(DATEDIF(MIN(MyTable[Timestamp])-WEEKDAY(MIN(MyTable[Timestamp]))+1,[@[Cutoff]],"d")/7,0)

To explain, for anybody who needs it...

To get the weekly average count of rows/records between two dates, I need two figures :

1. The number of rows/records between those two dates
2. The number of weeks covered between those two dates

And simply divide one by the other

The number of rows/records between the two dates is straightforward - the same COUNTIF numerator as before :

=COUNTIF(MyTable[Timestamp],"<="&[@[Cutoff]])

To get the number of weeks, you take the start date (which is simply the earliest date in the dataset) :

=MIN(MyTable[Timestamp])

Then determine the Sunday prior to that date :

=MIN(MyTable[Timestamp])-WEEKDAY(MIN(MyTable[Timestamp]))+1

Then determine the number of days between that Sunday and the arbitrary end date

=DATEDIF(MIN(MyTable[Timestamp])-WEEKDAY(MIN(MyTable[Timestamp]))+1,[@[Cutoff]],"d")

Then divide that number by 7 and round the result up to get the number of weeks in play :

=ROUNDUP(DATEDIF(MIN(MyTable[Timestamp])-WEEKDAY(MIN(MyTable[Timestamp]))+1,[@[Cutoff]],"d")/7,0)

Resulting in the formula above.

The reason I move the start date back to the previous Sunday is to get around the problem of a start date of, say, Friday, and an end date of the following Monday. Those dates are in two different weeks so the result should be 2 but the number of days between a consecutive Friday and Monday is only 3 so ROUNDUP(3/7) will only give you 1. By moving the start date back to the beginning of the initial week, you ensure you will count the number of whole weeks covered by the two dates, irrespective of the day of the week of either.

Hope this helps somebody...