6 Week Rolling Average

[Anonymous]

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. 

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Anonymous]

Hi @lbendlin , hopefully this sample data helps. I appreciate the help. 

Product ID	Sold	Customer	Date
2010	331	A	7/10/2023
1776	471	B	7/11/2023
2908	603	A	7/12/2023
2474	620	C	7/13/2023
2892	341	C	7/13/2023
1824	302	B	7/17/2023
2597	581	B	7/18/2023
2483	335	A	7/18/2023
2682	240	A	7/20/2023
1203	284	C	7/21/2023

[lbendlin]

Not really.  If you want a 6 week rolling average you need to provide at least 7 but better 12 or more weeks of sample data.