6 Week Rolling Average

I need to calculate a six week rolling average with duplicates of product ID removed before calculating the average. I have looked at lots of other people's posts about similar situations which is how I got the two attempts below. I just can't figure it out.

My product ID is a text format and the date is a date/time format. I don’t want to create a calculated column either and just create a measure to be used in a visualization.

I essentially want to sum the number of times all customers bought by week.

I have created a measure to calculate six week rolling average but I haven’t attempted how to remove duplicates. When I try adding my measure to a visual it says that “the date column contains duplicate dates. “ Which is true, customers buy on the same day multiple times.

My 1st attempt:

Rolling Average = var range = DATESINPERIOD(table[date],MIN(table[date]),-42, DAY) var first = FIRSTDATE(range) var last = LASTDATE(range) return IF(DATEDIFF(first, last, DAY)> 40, DIVIDE(CALCULATE(SUM(table[product ID]),ALL(table),range),CALCULATE(COUNTROWS(table),ALL(table),range)),BLANK())

My 2nd attempt:

Rolling Average 2 = CALCULATE(SUM(table[ID]),DATESINPERIOD(table [date],LASTDATE(table [date]),-42,DAY),ALL(table [customer]))

I created what I want as a pivot table in excel but this needs to be updated each week automatically. Below is what I want:

Any advice would be appreciated. Thank you.