Sum By Week accounting for zero instances

[tourless]

Hi Folks,

I have a simple three column sorted list of customers(a), dates(b), and revenue(c). I'm looking for a way to sum each customers revenue by week (monday to sunday) starting 2018-01-01, and accounting for instances of weeks with no data by inserting a row. Essentially turning this...

Name Date Revenue
Customer 1 2018-01-09 71.25
Customer 1 2018-01-17 88
Customer 1 2018-01-25 111.25
Customer 1 2018-02-08 47.25
Customer 1 2018-02-15 77.165
Customer 1 2018-02-22 61.165
Customer 1 2018-03-01 77.165
Customer 1 2018-03-08 77.165
Customer 1 2018-03-15 72.995
Customer 1 2018-03-22 79.095
Customer 1 2018-03-29 79.095
Customer 1 2018-04-12 113.16
Customer 1 2018-04-19 75.245
Customer 1 2018-04-26 124.49
Customer 2 2018-01-11 48.25
Customer 2 2018-01-18 49.5
Customer 2 2018-01-25 34.5
Customer 2 2018-02-01 49.5
Customer 2 2018-02-08 48.25
Customer 2 2018-02-15 27
Customer 2 2018-02-19 20
Customer 2 2018-02-22 37
Customer 2 2018-03-01 48.25
Customer 2 2018-03-08 47
Customer 2 2018-03-16 64
Customer 2 2018-03-23 43.25
Customer 2 2018-03-30 40.75
Customer 2 2018-04-20 48.25
Customer 3 2018-01-15 283.1
Customer 3 2018-01-18 215.75
Customer 3 2018-01-19 22.5
Customer 3 2018-01-22 271.2
Customer 3 2018-01-23 27
Customer 3 2018-01-25 419.2
Customer 3 2018-01-26 50
Customer 3 2018-01-29 225.6
Customer 3 2018-02-01 242
Customer 3 2018-02-05 267.9
Customer 3 2018-02-08 241.5
Customer 3 2018-02-12 209.8
Customer 3 2018-02-15 236.25
Customer 3 2018-02-19 284.4
Customer 3 2018-02-22 214
Customer 3 2018-02-26 205.2
Customer 3 2018-03-01 203.5

Into this...
Name WeekEndingDate Revenue
Customer 1 2018-01-07 0
Customer 1 2018-01-14 71.25
Customer 1 2018-01-21 88
Customer 1 2018-01-28 111.25
Customer 1 2018-02-04 0
Customer 1 2018-02-11 47.25
Customer 1 2018-02-18 77.165
Customer 1 2018-02-25 61.165
Customer 1 2018-03-04 0
Customer 1 2018-03-11 77.165
Customer 1 2018-03-18 72.995
Customer 1 2018-03-25 79.095
Customer 1 2018-04-01 79.095
Customer 1 2018-04-08 0
Customer 1 2018-04-15 113.16
Customer 1 2018-04-22 75.245
Customer 1 2018-04-29 124.49
Customer 2 2018-01-07 0
Customer 2 2018-01-14 48.25
Customer 2 2018-01-21 49.5
Customer 2 2018-01-28 34.5
Customer 2 2018-02-04 49.5
Customer 2 2018-02-11 48.25
Customer 2 2018-02-18 27
Customer 2 2018-02-25 57
Customer 2 2018-03-04 48.25
Customer 2 2018-03-11 47
Customer 2 2018-03-18 64
Customer 2 2018-03-25 43.25
Customer 2 2018-04-01 40.75
Customer 2 2018-04-08 0
Customer 2 2018-04-15 0
Customer 2 2018-04-22 48.25
Customer 3 2018-01-07 0
Customer 3 2018-01-14 0
Customer 3 2018-01-21 521.35
Customer 3 2018-01-28 767.4
Customer 3 2018-02-04 467.6
Customer 3 2018-02-11 509.4
Customer 3 2018-02-18 446.05
Customer 3 2018-02-15 498.4
Customer 3 2018-03-04 408.7

 

[tourless]

I think I've got it... added helper column to convert each date to it's week ending date and created a pivot table summing the revenue by week, by customer.