Power Pivot DAX average calculation of a time period

Hi,
i'd like to calculate the average of a cumulative figures by a given time interval, so example 7 days, so the total for a rolling 7 day period would be divided by 7, not the number of figures in that interval;

So if between 11th and the 17th there were 2 returns that that would be the total divided by 7 not 2.

Just dividing by 7 is fine once you have more than seven dates, in excel I'd use ROW() inside an if statement as the divosor;

=IF( ROW(1:1)<7, 

                  SUMIFS([Sales],[Date],"<="&[@Date],[Date],">"&[@Date]-7)/ROW(1:1)

                                                                                ,SUMIFS([Sales],[Date],"<="&[@Date],[Date],">"&[@Date]-7)/7)

I've been trying to come up with something similar in DAX, but can't find a function similar to ROW() ,

is there a way around this I was going to try datesbetween but if anyone has any suggestions ( helpfull 😀 )

workbook attached.

Richard

maybe

https://learn.microsoft.com/en-us/dax/row-function-dax

BTW, I did realize after posting I'd overcomplicated, and I could use the count for everything, I just had the excel structure on the brain.

Richard.

I've used this but will go through your suggestion,

7 day rolling:=VAR mdate = MAX('Calendar'[Date]) 
VAR anser = 
CALCULATE([Tsales],DATESINPERIOD('Calendar'[Date],mdate-6,7,DAY))
RETURN anser

Then to get 1,2,3,4,5,6 and then 7 onwards,

count:=VAR mdate = MAX('Calendar'[Date]) 
VAR drange = DATESBETWEEN('Calendar'[Date],mdate-6,mdate ) 
RETURN 
COUNTROWS(drange)

and lastly

Rolling Avg by Count:=VAR div1 = [7 day rolling] / [count]
VAR count1 = [count]
VAR anser2 = [7 day rolling]/7
RETURN
IF(count1 <7 ,div1,anser2 )

Seem to work, I'll let you know how I get on with your suggestion,

Richard.