Rolling Average w/ Multiple Entries Per Date

[MartyR]

Hi all.  Still very much a beginner at DAX so any help would be much appreciated.  I'm trying to get a rolling average of Product Distribution, but when I use the DAX codes I can find online for rolling averages, it creates an error as the date column contains duplicate dates.  The code I'm trying to use is;

Product X Rolling 4Wk =

VAR NumDays = 28

VAR RollingSum =

CALCULATE(

SUM('Raw Data'[Product X In Store?]),

DATESINPERIOD('Raw Data'[Call Date],LASTDATE('Raw Data'[Call Date]),-NumDays,DAY)

)

RETURN

RollingSum/NumDays

Example of type of data is as follows.  We show this as weekly average % distribution currently, but client has requested visibility of 4 week rolling average.

Call Date	Store Name	Product X In Store?
06/09/21	Store A	1
06/09/21	Store B	0
06/09/21	Store C	0
07/09/21	Store D	1
07/09/21	Store E	1
07/09/21	Store F	1
08/09/21	Store G	0
08/09/21	Store H	1
08/09/21	Store I	0

Any help would be greatly appreciated!

[MartyR]

Thanks, and great to know it's doable.  Will give that a go!

[Schmidtmayer]

Just create a table with unique date values and establish a connection between your initial table and your newly created dates table. Then change your measure to the date column of the newly created dates table. You may use CALENDARAUTO function (DAX) - DAX | Microsoft Docs for that.